create table test (C1 varchar(50),C2 varchar(1))
insert test
select 'a','Y'
union
select 'b','Y'
union
select 'c','N'
go--这里开始,要怎么改为动态语句呢,同时表名也有可能是变量的,字段的值也会是变量的
declare @code varchar(50)
set @code = ''
select @code = @code + ';' + C1 from test where C2 = 'Y'
print @code
--这里结束drop table test
insert test
select 'a','Y'
union
select 'b','Y'
union
select 'c','N'
go--这里开始,要怎么改为动态语句呢,同时表名也有可能是变量的,字段的值也会是变量的
declare @code varchar(50)
set @code = ''
select @code = @code + ';' + C1 from test where C2 = 'Y'
print @code
--这里结束drop table test
--动态SQLdeclare @code varchar(50),@tablename varchar(10),@sql nvarchar(500)
set @code = ''
set @tablename='test'
set @sql='select @c=@c+'';''+C1 from '+@tablename+' where C2 = ''Y'''
exec sp_executesql @sql,N'@c varchar(50) output',@code output
print @code
create table test (C1 varchar(50),C2 varchar(1))
insert test
select 'a','Y'
union
select 'b','Y'
union
select 'c','N'
go
declare @table varchar(10), @field varchar(10),@sql varchar(8000)
set @table='test'
set @field='C2'
set @sql=' select * from '+@table+ ' where '+@field+'=''Y'''
--print @sql
exec(@sql)drop table test
insert test
select 'a','Y','Y'
union
select 'b','Y','N'
union
select 'c','N','N'
go--这里开始,要怎么改为动态语句呢,同时表名也有可能是变量的,字段的值也会是变量的
declare @code varchar(50)
set @code = ''
select @code = @code + ';' + C1 from test where C2 = 'Y'
print @code
--这里结束
go--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500)
set @code = ''
set @tablename='test'
set @sql='select @c=@c+'';''+C1 from '+@tablename+' where C2 = ''Y'''
exec sp_executesql @sql,N'@c varchar(50) output',@code output
print @codego--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500),@c2 varchar(50),@c3 varchar(50)
set @code = ''
set @tablename='test'
set @c2 = 'Y'
set @c3 = 'Y'
set @sql='select @c=@c+'';''+C1 from '+@tablename+' where C2 = @c2 and C3 = @c3'
exec sp_executesql @sql,N'@c2 varchar(50),@c3 varchar(50),@c varchar(50) output',@c2,@c3,@code output
print @code
godrop table test
insert test
select 'a','Y','Y'
union
select 'b','Y','N'
union
select 'c','N','N'
union
select 'd','YY','Y'
go--这里开始,要怎么改为动态语句呢,同时表名也有可能是变量的,字段的值也会是变量的
declare @code varchar(50)
set @code = ''
select @code = @code + ';' + C1 from test where C2 = 'Y'
print @code
--这里结束
go--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500)
set @code = ''
set @tablename='test'
set @sql='select @c=@c+'';''+C1 from '+@tablename+' where C2 = ''Y'''
print '1:'+@sql
exec sp_executesql @sql,N'@c varchar(50) output',@code output
print @codego--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500),@c2 varchar(50),@c3 varchar(50)
set @code = ''
set @tablename='test'
set @c2 = 'Y'
set @c3 = 'Y'
set @sql='select @c=@c+'';''+C1 from '+@tablename+' where C2 = @c2 and C3 = @c3'
print '2:'+@sql
exec sp_executesql @sql,N'@c2 varchar(50),@c3 varchar(50),@c varchar(50) output',@c2,@c3,@code output
print @code
go--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500),@c2 varchar(50),@c3 varchar(50),@zd_c1 varchar(50),@zd_c2 varchar(50)
set @code = ''
set @tablename='test'
set @c2 = 'Y'
set @c3 = 'N'
set @zd_c1 = 'C1'
set @zd_c2 = 'C2'
set @sql='select @c=@c+'';''+'+ @zd_c1 +' from '+@tablename+' where '+ @zd_c2 +' = @c2 and C3 = @c3'
print '3:'+@sql
exec sp_executesql @sql,N'@c2 varchar(50),@c3 varchar(50),@c varchar(50) output',@c2,@c3,@code output
print @code
go--动态SQL
declare @code varchar(50),@tablename varchar(10),@sql nvarchar(500),@c2 varchar(50),@c3 varchar(50),@zd_c1 varchar(50),@zd_c2 varchar(50)
set @code = ''
set @tablename='test'
set @c2 = 'Y'
set @c3 = 'Y'
set @zd_c1 = 'C1'
set @zd_c2 = 'C2'
set @sql='select @c=@c+'';''+'+ @zd_c1 +' from '+@tablename+' where '+ @zd_c2 +' like ''%''+@c2+''%'' and C3 = @c3'
print '4:'+@sql
exec sp_executesql @sql,N'@c2 varchar(50),@c3 varchar(50),@c varchar(50) output',@c2,@c3,@code output
print @code
godrop table test
其实还可以考以下的贴子!http://topic.csdn.net/t/20061203/22/5203669.html
exec SP_executesql中的like条件使用参数的问题(高分求教)http://topic.csdn.net/t/20060911/10/5012580.html
如何用sp_executesql实现where id in (1,2)http://topic.csdn.net/t/20051128/09/4422149.html
存储过程返回结果集怎么union?