求助：将 varchar 转换为数据类型 numeric 时出现算术溢出错误。

[7年]

alter PROCEDURE [dbo].[LD_UpdateWater](
 @freenum int
)
AS
declare
 @acco_code varchar(10),--账户ID
 @water_num varchar(20),--s使用流量，对应meal_card_code字段
 @cons_money money,--消费金额
 @watersn int,--第一条记录sn
 @watersn2 int,--其他记录sn
 @moneyleft money,-- 第一条记录余额
 @moneyleft1 money, --其他记录余额
 @conswaternum varchar(10) --流量


BEGIN
	SET NOCOUNT ON;
   --查询出采集一次的账户信息
   declare cursor3 cursor for
   select meal_card_code -@freenum*2,acco_code from bs_cons_info where acco_code in
   (select acco_code from bs_cons_info group by acco_code having count(*)=1);
   open cursor3;
   fetch next from cursor3 into @water_num,@acco_code;
   while @@fetch_status = 0 
    begin
	  --print(@water_num) 
      if convert(numeric(10,2),@water_num) > 0 --此处出错
        begin
          update bs_cons_info set meal_card_code = meal_card_code - @freenum where acco_code=@acco_code;
          update bs_cons_info set cons_money= @water_num * 2.4,money_left= @water_num * -2.4
          where acco_code=@acco_code;
        end else
        begin
          update bs_cons_info set cons_money = 0.0,money_left = 0.0 where acco_code=@acco_code;
        end
     fetch next from cursor3 into @water_num,@acco_code
    end 
   close cursor3;
   deallocate cursor3;
   end

[开着拖拉机泡妞]

改成
 if convert(numeric(20,2),@water_num) > 0

[让你望见影子的墙]

@water_num varchar(20), 你先输出下这个变量的值，可能超出了numeric(10,2)所能表示的数据范围

[zczhangchao2003]

--print(@water_num)
显示多少 ？

[7年]

好，我试试

[7年]

当@water_num是正数的时候报错，负数就不报错了。。。

[开着拖拉机泡妞]

还是范围的问题。你去百度一下numeric的用法吧，把里面两个参数弄明白

[7年]

[Quote=引用 6 楼 的回复:]

还是范围的问题。你去百度一下numeric的用法吧，把里面两个参数弄明白
[/Quote]
看了，但还是没明白。。求指教

[anzhiqiang_touzi]

改成
if convert(numeric(20,2),@water_num) > 0

[开着拖拉机泡妞]

decimal and numeric (Transact-SQL)Numeric data types that have fixed precision and scale. 

decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )] 
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision) 
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) 
The maximum number of decimal digits that can be stored to the right 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. Maximum storage sizes vary, based on the precision.

Precision
 Storage bytes
 
1 - 9
 5
 
10-19
 9
 
20-28
 13
 
29-38
 17
 

  Converting decimal and numeric Data 
For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.

Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

[7年]

update bs_cons_info set cons_money= @water_num * 2.4,money_left= @water_num * -2.4

问题在这！！！！结贴了，谢谢各位