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,[email protected],@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,[email protected],@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,[email protected],@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)+ ' ' ', ' ' '[email protected]+ ' ' ', '+rtrim(@sl)+ ', '+rtrim(@ydlx)+ ', ' ' '[email protected]+ ' ' ', ' ' '[email protected]+ ' ' ', ' ' '[email protected]+ ' ' ') '
when '102 ' then
'insert into table1(1,2,3,4,5,6,7)values(@data,@fh,[email protected],@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,[email protected],@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,[email protected],@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,[email protected],@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,[email protected],@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,[email protected],@ydlx,@dh,@ph,@bz)
GO