Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server decimal variable assignment?

I'm trying to write a stored procedure and I'm getting unexpected divide by 0 exceptions.

I've narrowed it down to the following example.

Why in the world does this :

    declare @A decimal;
    declare @B decimal;
    declare @C decimal;

    set @A = 4;
    set @B = 9;
    set @C = @A/@B 

    select @A/@B as 'Expected'
    select @C as 'Wut'

result in this?

    Expected
    ---------------------------------------
    0.4444444444444444444

    (1 row(s) affected)

    Wut
    ---------------------------------------
    0

    (1 row(s) affected)
like image 794
Balt Avatar asked Apr 11 '14 15:04

Balt


People also ask

How do you set decimals in SQL server?

In standard SQL, the syntax DECIMAL( M ) is equivalent to DECIMAL( M ,0) . Similarly, the syntax DECIMAL is equivalent to DECIMAL( M ,0) , where the implementation is permitted to decide the value of M . MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10.

How do you pass a decimal value in SQL?

For instance, decimal (4,2) indicates that the number will have 2 digits before the decimal point and 2 digits after the decimal point, something like this has to be the number value- ##. ##. One important thing to note here is, – parameter s (Scale) can only be specified if p (Precision) is specified.

How do you assign a value to a variable in SQL?

To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.


1 Answers

The problem is that you haven't specified a scale for the decimal type. From MSDN:

s (scale)

The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p.

So when you try to store @A/@B back into @C, the fractional part gets truncated.

Notice:

declare @A decimal(18, 3);
declare @B decimal(18, 3);
declare @C decimal(18, 3);

set @A = 4;
set @B = 9;
set @C = @A/@B 

select @A/@B -- 0.44444444444444444444
select @C    -- 0.444
like image 141
p.s.w.g Avatar answered Oct 17 '22 11:10

p.s.w.g