set @sqlstr1='insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = @objid order by colid' --> set @sqlstr1='insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = '+cast(@objid as varchar)+' order by colid'
那在EXEC里面可以定义变量吗?
@cur_prf这个参数在哪里定义?ALTER procedure line_tran @linesum int,@comp_no int,@forth_num int,@tbl_name varchar(20),@yearper int as begin truncate table temp1 declare @objid int,@line_temp int,@sqlstr1 nvarchar(4000),i int,@j int
select @objid = id from sysobjects where id = object_id(@tbl_name) set @sqlstr1=N'insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = @objid order by colid' exec sp_executesql @sqlstr1,N'@objid int',@objid set @i=1 set @sqlstr1='declare '
while @i<=@linesum begin if @i=1 set @sqlstr1=@sqlstr1+'@cur_prf'+cast(@i as varchar)+' varchar(30)' else set @sqlstr1=@sqlstr1+',@cur_prf'+cast(@i as varchar)+' varchar(30)' set @i=@i+1 end
exec sp_executesql @sqlstr1,N'@cur_prf ...',@cur_prf end go
一次执行exec就是一个连接,不同连接定义的变量不能通用,只能用参数传递 一个exec里面是可以定义变量的 如: set @sqlstr1='declare @i int set @i='+cast(@inputi as varchar)+' select * from test where id=@i' exec (@sqlstr1)
又是内外部变量问题. declare @i int set @i=1 exec('select * from tb where id=@i') 这当然错了. exec内外部编译进程不同,空间不同,在外部定义的@i在内部当然是未声明的了.exec('select * from tb where id=' + @i) 或者用sp_executesql
第一个错误: 把开始处的: set @sqlstr1='insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = @objid order by colid' 改成: set @sqlstr1='insert into tbltemp(project) select top ' + rtrim(@linesum) + ' name from syscolumns where id = ' + rtrim(@objid) /*修改此处,变成字符型*/ + ' order by colid'第二个错误可能是由于第一个引起的.先改正第一个错误再试试.
可以用sp_executesql來傳入參數
-->
set @sqlstr1='insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = '+cast(@objid as varchar)+' order by colid'
as
begin
truncate table temp1
declare @objid int,@line_temp int,@sqlstr1 nvarchar(4000),i int,@j int
select @objid = id from sysobjects where id = object_id(@tbl_name)
set @sqlstr1=N'insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = @objid order by colid'
exec sp_executesql @sqlstr1,N'@objid int',@objid set @i=1
set @sqlstr1='declare '
while @i<=@linesum
begin
if @i=1
set @sqlstr1=@sqlstr1+'@cur_prf'+cast(@i as varchar)+' varchar(30)'
else
set @sqlstr1=@sqlstr1+',@cur_prf'+cast(@i as varchar)+' varchar(30)' set @i=@i+1
end
exec sp_executesql @sqlstr1,N'@cur_prf ...',@cur_prf
end
go
---------------------------------------------------------------------------------------------
如果动态SQL语句内还有变量,需要采用exec sp_executesql方式执行动态SQL
execute sp_executesql @sqlstr1,N'@objid int',@objid
一个exec里面是可以定义变量的
如: set @sqlstr1='declare @i int set @i='+cast(@inputi as varchar)+' select * from test where id=@i'
exec (@sqlstr1)
declare @i int
set @i=1
exec('select * from tb where id=@i')
这当然错了. exec内外部编译进程不同,空间不同,在外部定义的@i在内部当然是未声明的了.exec('select * from tb where id=' + @i)
或者用sp_executesql
把开始处的:
set @sqlstr1='insert into tbltemp(project) select top '+cast(@linesum as varchar)+' name from syscolumns where id = @objid order by colid'
改成:
set @sqlstr1='insert into tbltemp(project) select top ' + rtrim(@linesum) + ' name from syscolumns where id = ' +
rtrim(@objid) /*修改此处,变成字符型*/
+ ' order by colid'第二个错误可能是由于第一个引起的.先改正第一个错误再试试.