变量取值问题
declare @where nvarchar(4000)
declare @data nvarchar(50)
set @where = 'ID = ''1'''
exec('select @data = name from #t1 where '+@where+'')
#t1 有ID,name 两列
其中@where是条件,想要赋值于@data
应该怎么写
[解决办法]
得用sp_executesql:
declare @where nvarchar(4000)
declare @data nvarchar(50)
declare @tmpsql nvarchar(4000)
set @where = 'ID = ''1'''
set @tmpsql = 'select @data = name from #t1 where '+@where
exec sp_executesql @tmpsql,
N'@data nvarchar(50) output',
@data out
select @data
create table #t1(id int,[name] varchar(20), remark varchar(20))
insert into #t1
select 1 ,'张三1' ,'备注1' union all
select 2 ,'张三2' ,'备注2' union all
select 3 ,'张三3' ,'备注3' union all
select 4 ,'张三4' ,'备注4' union all
select 5 ,'张三5' ,'备注5'
drop table 表A
declare @where nvarchar(4000)
declare @sql nvarchar(4000)
set @where = ' ID = ''1'''
set @sql =' declare @data nvarchar(50);select @data = name from #t1 where '+@where+''
exec(@sql)