set @name='username' select @sql='select '+@value+' = '+@name+' from table1 where userid=1' exec(@sql) print @value 以上语句中的@value和@name的声明和exec(@sql) 不在同一个会话期,所以无法返回正确的@value。 正确的方法可以借助临时表。set @name='username' select @sql='select '+@name+' as Value into #Table2 from table1 where userid=1' exec(@sql) select @value=Value from #Table2 print @value
把@value的赋值和显示也放进@sql
用临时表可以解决这个问题,但用 sp_executesql好象还是有点问题,哪位高手能用 sql_executesql 写一下declare @a1 int declare @sql nvarchar(100) declare @column varchar(30) declare @ncolumn varchar(30) declare @nid int select @ncolumn='username' set @nid=5 select @sql=N'select @value=@name from table1 where userid=@id' exec sp_executesql @sql,N'@value char(30) output,@name char(30),@id int',@column output,@ncolumn,@nid print @column
declare @a1 int declare @sql nvarchar(100) declare @column varchar(30) declare @ncolumn varchar(30) declare @nid intselect @ncolumn='username' set @nid=5 select @sql=N'select @value=' + @ncolumn + N' from table1 where userid=@id' exec sp_executesql @sql,N'@value char(30) output,@id int',@column output,@nid print @column
declare @a1 int declare @sql nvarchar(100) declare @column varchar(30) declare @ncolumn varchar(30) declare @nid intselect @ncolumn='username' set @nid=5 select @sql=N'select @value=' + @ncolumn + N' from table1 where userid=@id' exec sp_executesql @sql,N'@value varchar(30) output,@id int',@column output,@nid print @column
select @sql='select '+@value+' = '+@name+' from table1 where userid=1'
exec(@sql)
print @value
以上语句中的@value和@name的声明和exec(@sql)
不在同一个会话期,所以无法返回正确的@value。
正确的方法可以借助临时表。set @name='username'
select @sql='select '+@name+' as Value into #Table2 from table1 where userid=1'
exec(@sql)
select @value=Value from #Table2
print @value
declare @sql nvarchar(100)
declare @column varchar(30)
declare @ncolumn varchar(30)
declare @nid int
select @ncolumn='username'
set @nid=5
select @sql=N'select @value=@name from table1 where userid=@id'
exec sp_executesql @sql,N'@value char(30) output,@name char(30),@id int',@column output,@ncolumn,@nid
print @column
declare @sql nvarchar(100)
declare @column varchar(30)
declare @ncolumn varchar(30)
declare @nid intselect @ncolumn='username'
set @nid=5
select @sql=N'select @value=' + @ncolumn + N' from table1 where userid=@id'
exec sp_executesql @sql,N'@value char(30) output,@id int',@column output,@nid
print @column
declare @sql nvarchar(100)
declare @column varchar(30)
declare @ncolumn varchar(30)
declare @nid intselect @ncolumn='username'
set @nid=5
select @sql=N'select @value=' + @ncolumn + N' from table1 where userid=@id'
exec sp_executesql @sql,N'@value varchar(30) output,@id int',@column output,@nid
print @column