SQL Server十进制变量分配? [英] SQL Server decimal variable assignment?
问题描述
我正在尝试编写一个存储过程,但是我得到了意外的除以0的异常。
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.
为什么要这样做:
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'
导致此结果?
Expected
---------------------------------------
0.4444444444444444444
(1 row(s) affected)
Wut
---------------------------------------
0
(1 row(s) affected)
推荐答案
问题是您尚未为十进制
类型指定比例。来自 MSDN :
The problem is that you haven't specified a scale for the decimal
type. From MSDN:
s(scale)
将要存储在右边的小数位数小数点的从p中减去此数字可确定小数点左边的最大位数。小数位数必须是0到p之间的值。仅在指定精度的情况下才能指定比例。 默认比例为0 ;因此,0< = s< = p。
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.
因此,当您尝试存储 @ A / @ B
返回到 @C
,小数部分被截断。
So when you try to store @A/@B
back into @C
, the fractional part gets truncated.
注意:
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
这篇关于SQL Server十进制变量分配?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!