Geek Answers Handbook

SQL Server Decimal Variable Assignment?

I am trying to write a stored procedure and I get an unexpected split on 0 exceptions.

I 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'

lead to this?

  Expected --------------------------------------- 0.4444444444444444444 (1 row(s) affected) Wut --------------------------------------- 0 (1 row(s) affected)
+6
source share
2 answers

The problem is that you did not specify a scale for the decimal type. From MSDN :

s (scale)

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

Therefore, when you try to save @A/@B back to @C , the fractional part becomes truncated.

Note:

 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
+10
source

This is because if you do not declare accuracy and scale, the Decimal value indicates (18, 0) 18 digits to the left of the decimal point and 0 to the right.

If you change C like this:

 declare @A decimal; declare @B decimal; declare @C decimal (18, 9); set @A = 4; set @B = 9; set @C = @A/@B select @A/@B as 'Expected' select @C as 'Wut'

You should get the right answer.

 Expected --------------------------------------- 0.4444444444444444444 (1 row(s) affected) Wut --------------------------------------- 0.444444444 (1 row(s) affected)
0
source

More articles:


All Articles