服务器: 消息 137，级别 15，状态 1，行 1 必须声明变量 '@num'。该如何解决

服务器: 消息 137，级别 15，状态 1，行 1 必须声明变量 '@num'。

SQL code

alter proc zhilincafen @itemno varchar(50),@zilinno varchar(50),@cafenshu intasdelete from  zilintab_lc_d where itemno =@itemnodeclare @sql varchar(8000)     set @sql=''declare @sql2 varchar(8000)    set @sql2=''declare @num int               declare @xuhao varchar(10)     set @xuhao='001'declare @i int  set @i=0  while @i<=25begin    set @sql2='select @num=z'+cast(@i as varchar(10))+' from zilintab where itemno= '''+@zilinno+''''select @sql2    exec(@sql2)    while @num>0    begin                    if @num>@cafenshu        begin        set @sql='insert into zilintab_lc_d(zilinno,z'+cast(@i as varchar(10))+') select '+@zilinno+'-'+right('00'+@xuhao,3)+','+cast(@num as varchar(10))                exec(@sql)        end        else if @num<@cafenshu        begin         set @sql='insert into zilintab_lc_d(zilinno,z'+cast(@i as varchar(10))+') select '+@zilinno+'-'+right('00'+@xuhao,3)+','+cast(@cafenshu as varchar(10))                exec(@sql)        end    set @xuhao=@xuhao+1    set @num=@num-@cafenshu            end    set @i=@i+1end

[解决办法]
把声明@num变量写到动态语句中就行了
[解决办法]
exec(sqlString)
#1.exec外部定义的变量在exec作用域中不可见
#2.exec内部定义的变量在exec外部也不可见

SQL code

--#1.DECLARE @tempTable TABLE(    num INT)--#2.DELETE FROM @tempTableINSERT INTO @tempTableEXEC(sqlString)--#3.SELECT @num = num FROM @tempTable
[解决办法]
lz 外面还用到 @num  那就这样  在懂太语句中声明一个变量 把@@num传进去
SQL code
set sql2='daclare @num int'set sql2=sql2+char(10)+'set @num='+@num+''set @sql2=sql2+char(10)+'select @num=z'+cast(@i as varchar(10))+' from zilintab where itemno= '''+@zilinno+''''
[解决办法]
探讨

lz 外面还用到 @num 那就这样 在懂太语句中声明一个变量 把@@num传进去

SQL code

set sql2='daclare @num int'
set sql2=sql2+char(10)+'set @num='+@num+''
set @sql2=sql2+char(10)+'select @num=z'+cast(@i as varchar(10))+' from……
 

 

查看更多 下一篇