SQL Server十进制变量分配？

Question

我正在尝试编写一个存储过程，但是我得到了意外的除以0的异常。

我将其范围缩小到以下示例。

为什么在世界上这样做:

    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)

Answer 1

问题是您尚未为decimal类型指定比例。从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.

因此，当您尝试将 @A/@B存储回 @C时，小数部分会被截断。

注意:

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