create table tbb(id decimal(18,0))create table br_mascustomer(id int) insert into br_mascustomer select 1 union all select 2 union all select 3 union all select 5 union all select 6declare @sql nvarchar(200) declare @mastid varchar(40) set @mastid='1,3,6'set @sql='insert into tbb select id from br_mascustomer where id in('+@mastid+')' exec(@sql)select * from tbb /* id --------------------------------------- 1 3 6 */
@tb 是表变量 #t 是临时表 但是这种情况临时表也是不行的。
--举例说明一下 --这种是可以的 declare @t varchar(100) set @t='create table #t (id int) insert into #t select 1 select * from #t' exec (@t) /* id ----------- 1 */--这个就不行了 declare @t1 varchar(100) set @t1='create table #t (id int)' declare @t2 varchar(100) set @t2='insert into #t select 1 select * from #t' exec (@t1) exec (@t2) /* Invalid object name '#t'. */
函数里面就不用想动态了,外面写得天花乱坠,函数里面都不允许执行。不用动态直接这样:insert @tb select id from tbname where ','+@mastid+',' like '%,'+ltrim(id)+',%'
exec 'select id from .....'
create table tbb(id decimal(18,0))create table br_mascustomer(id int)
insert into br_mascustomer
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6declare @sql nvarchar(200)
declare @mastid varchar(40)
set @mastid='1,3,6'set @sql='insert into tbb select id from br_mascustomer where id in('+@mastid+')'
exec(@sql)select * from tbb
/*
id
---------------------------------------
1
3
6
*/
#t 是临时表
但是这种情况临时表也是不行的。
--举例说明一下
--这种是可以的
declare @t varchar(100)
set @t='create table #t (id int) insert into #t select 1 select * from #t'
exec (@t)
/*
id
-----------
1
*/--这个就不行了
declare @t1 varchar(100)
set @t1='create table #t (id int)'
declare @t2 varchar(100)
set @t2='insert into #t select 1 select * from #t'
exec (@t1)
exec (@t2)
/*
Invalid object name '#t'.
*/