关于存储过程的声明变量问题,着急啊!!!,请各路神仙帮忙啊!
CREATE   procedure   lsj_insert  
@data   smalldatetime,@fh   varchar(20),@sl   int,@ydlx   int,@dh   varchar(50),@ph   varchar(50),@bz   varchar(100)
as
declare   @sql   varchar(1000)
set   @sql   =   (case   @ydlx
when   '101 '   then
'insert   into   table(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz) '
when   '102 '   then
'insert   into   table1(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
when   '103 '   then
'insert   into   table2(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz) '
when   '104 '   then
'insert   into   table3(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
when   '105 '   then
'insert   into   table4(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz) '
when   '106 'then
'insert   into   table5(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
else   ' '
end)
print(@sql)
exec(@sql)
GO
运行完后提示:
服务器:   消息   137，级别   15，状态   2，行   1
必须声明变量   '@data '。
请问这个问题怎么解决,请各位高手指导指导,不胜感激.


------解决方案--------------------
alter procedure lsj_insert(
@data smalldatetime,@fh varchar(20),@sl int,@ydlx int,@dh varchar(50),@ph varchar(50),@bz varchar(100))
as
declare @sql varchar(1000)
set @sql = (case @ydlx
when '101 ' then
'insert into [table]([1],[2],[3],[4],[5],[6],[7])values( ' ' '+convert(char(19),@data,120)+ ' ' ', ' ' '+@fh+ ' ' ', '+rtrim(@sl)+ ', '+rtrim(@ydlx)+ ', ' ' '+@dh+ ' ' ', ' ' '+@ph+ ' ' ', ' ' '+@bz+ ' ' ') '
when '102 ' then
'insert into table1(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
when '103 ' then
'insert into table2(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz) '
when '104 ' then
'insert into table3(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
when '105 ' then
'insert into table4(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz) '
when '106 'then
'insert into table5(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz) '
else ' '
end)
print(@sql)
--xec(@sql)
GO

--按101那个改
------解决方案--------------------
不用動態語句，Case改用If來實現，代碼修改為如下。

CREATE procedure lsj_insert
@data smalldatetime,@fh varchar(20),@sl int,@ydlx int,@dh varchar(50),@ph varchar(50),@bz varchar(100)
as
if @ydlx = '101 '
insert into table1(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz)
else if @ydlx = '102 '
insert into table1(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz)
else if @ydlx = '103 '
insert into table2(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz)
else if @ydlx = '104 '
insert into table3(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz)
else if @ydlx = '105 '
insert into table4(1,2,3,4,5,6,7)values(@data,@fh,@sl,@ydlx,@dh,@ph,@bz)
else if @ydlx = '106 '
insert into table5(1,2,3,4,5,6,7)values(@data,@fh,-@sl,@ydlx,@dh,@ph,@bz)
GO