日期:2014-05-18  浏览次数:20721 次

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

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 --[color=#FF0000]此处出错[/color]
        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



------解决方案--------------------
SQL code

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

------解决方案--------------------
@water_num varchar(20), 你先输出下这个变量的值,可能超出了numeric(10,2)所能表示的数据范围
------解决方案--------------------
--print(@water_num) 
显示多少 ?
------解决方案--------------------
改成
 if convert(numeric(20,2),@water_num) > 0