存储过程中:
....
exec('select *,IDENTITY(int,1,1) as Num into #TempPhones from emp_mstr where '+@Parameter)
select XX,XX1,XX2 from #TempPhones
....
这样创建一个临时表以后我再想从临时表中取数据提示对象名#TempPhones无效，晕，如果我不是使用exec()执行sql语句的话就没有任何问题,WHY?
---------------------------------------------------------------
局部临时表属于创建他的连接,EXEC是一个的单独的连接.
可以这样:
....
exec('select *,IDENTITY(int,1,1) as Num into ##TempPhones from emp_mstr where '+@Parameter)
select XX,XX1,XX2 from ##TempPhones
....
OR:
....
exec('select *,IDENTITY(int,1,1) as Num into ##TempPhones from emp_mstr where '+@Parameter+ ' select XX,XX1,XX2 from #TempPhones')
....
---------------------------------------------------------------
先创建另一个临时表#TEMP2
select top 0 * into #temp2 from emp_mstr
alter table #temp2 add num int
insert into #temp2 exec('select *,IDENTITY(int,1,1) as Num into #TempPhones from emp_mstr where '+@Parameter)
select XX,XX1,XX2 from #Temp2
---------------------------------------------------------------
use pubs
exec
('SELECT *,
IDENTITY(smallint, 100, 1) AS job_num INTO #employees1
FROM employee
'+' select * from #employees1')