求解EXECUTE执行时遇到的问题。

Question

declare @sql nvarchar(400)
set @sql='select top 10 * into #abc from employees'
EXECUTE(@sql)
select * from #abc以上语句执行后提示错误找不到表#abc，原因是使用execute执行的语句与其外面的语句不在同一会话中，execute中语句执行完毕其会话也就结束了。因此@abc也就被释放了。如果换成全局临时表可解决问题。但又怕访问量大时会有冲突，该如何解决？

Answer 1

declare @sql nvarchar(400) 
set @sql='select top 10 * into #abc from employees select * from #abc ' 
EXECUTE(@sql) 

Answer 2

改用存储过程来实现，把 @abc 封装起来

Answer 3

declare @sql nvarchar(400) 
set @sql='select top 10 * into #abc from employees;select * from #abc' 
EXECUTE(@sql) 

Answer 4

declare @sql nvarchar(400) 
set @sql='select top 10 * into #abc from employees;select * from #abc' 
EXECUTE(@sql) 
-------------------------------------
另外提外话，实在看不出楼主为什么要用动态SQL。难道直接这样不行吗？
select top 10 * from employees

Answer 5

declare @sql nvarchar(400)  
set @sql='select top 10 * into ##abc from t_goods '  
EXECUTE(@sql)  select * from ##abc 

Answer 6

declare @sql nvarchar(400)  
set @sql='select top 10 * into #abc from employees select top 10 * from #abc '  
EXECUTE(@sql)  
这个和临时表的作用域有关 
你的#abc 这个临时表的作用域是在 EXECUTE(@sql) 内部
当在他外部是 就找不到该临时表

Answer 7

declare @sql nvarchar(400)  
--用分号把两条执行语句分隔开
set @sql='select top 10 * into #abc from employees; select * from #abc '  
EXECUTE(@sql)  

Answer 8

select * into #abc from sysobjects where  1= 2
declare @sql nvarchar(400) 
set @sql='insert into #abc select top 10 *   from sysobjects' 
EXECUTE(@sql) 
select * from #abc 
/*
name                                                                                                                             id          xtype uid    info   status      base_schema_ver replinfo    parent_obj  crdate                                                 ftcatid schema_ver  stats_schema_ver type userstat sysstat indexdel refdate                                                version     deltrig     instrig     updtrig     seltrig     category    cache  
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ------------------------------------------------------ ------- ----------- ---------------- ---- -------- ------- -------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ------ 
sysobjects                                                                                                                       1           S     1      25     -536870909  96              0           0           2000-08-06 01:29:12.717                                0       96          0                S    1        113     0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
sysindexes                                                                                                                       2           S     1      29     -536870907  32              0           0           2000-08-06 01:29:12.717                                0       32          0                S    1        8273    0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
syscolumns                                                                                                                       3           S     1      32     -536870909  80              0           0           2000-08-06 01:29:12.717                                0       80          0                S    1        113     0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
systypes                                                                                                                         4           S     1      20     -536870909  80              0           0           2000-08-06 01:29:12.717                                0       80          0                S    1        113     0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
syscomments                                                                                                                      6           S     1      10     -536870911  48              0           0           2000-08-06 01:29:12.717                                0       48          0                S    1        81      0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
sysfiles1                                                                                                                        8           S     1      4      -536870912  0               0           0           2000-08-06 01:29:12.717                                0       0           0                S    1        65      0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
syspermissions                                                                                                                   9           S     1      11     -536870911  16              0           0           2000-08-06 01:29:12.717                                0       16          0                S    1        81      0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
sysusers                                                                                                                         10          S     1      20     -536870909  96              0           0           2000-08-06 01:29:12.717                                0       96          0                S    1        113     0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
sysproperties                                                                                                                    11          S     1      5      -536870911  32              0           0           2000-08-06 01:29:12.717                                0       32          0                S    1        81      0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0
sysdepends                                                                                                                       12          S     1      11     -536870909  64              0           0           2000-08-06 01:29:12.717                                0       64          0                S    1        113     0        2000-08-06 01:29:12.717                                0           0           0           0           0           2           0（所影响的行数为 10 行）
*/
drop table #abc