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

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

alter proc zhilincafen @itemno varchar(50),@zilinno varchar(50),@cafenshu int
as
delete from  zilintab_lc_d where itemno =@itemno

declare @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<=25
begin

    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+1
end




------解决方案--------------------
把声明@num变量写到动态语句中就行了
------解决方案--------------------
exec(sqlString)
#1.exec外部定义的变量在exec作用域中不可见
#2.exec内部定义的变量在exec外部也不可见
SQL code
--#1.
DECLARE @tempTable TABLE
(
    num INT
)

--#2.
DELETE FROM @tempTable
INSERT INTO @tempTable
EXEC(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……