Question

SQLServer动态exec查询语句，返回查询变量

例：
create procedure tempPro
as
declare @sql nvarchar(1000),@user nvarchar(500),@tablename nvarchar(100)
set @tablename='T_Table'
set @sql='select '+@user+' =[TwoField] from '+@tablename+' where [OneField]=''hello'
exec sp_executesql @sql, @user OUTPUT
print @user
这样获取不到@user查询后的值，
请问如何获得@user的值呢？

Answer 1

drop procedure tempPro;
go

create procedure tempPro
as
declare
@sql nvarchar(200),
@user nvarchar(500),
@tablename nvarchar(100)
begin
set @tablename='t_stars'
set @sql=N'select @userOUT = stars_name from '+@tablename+' where stars_id=''hk1006''';
execute sp_executesql @sql, N'@userOUT varchar(30) output', @userOUT=@user output;
print @user;
end
go

exec tempPro;