在查詢分析器中直接執行
select pa_no,pa_peno into #tbPA00H from tempdb..tmp29
select * from #tbPA00H
可以得到我要的結果改用變量後卻不行
DECLARE @SQL varchar(100)
select @SQL='select pa_no,pa_peno into #tbPA00H from tempdb..tmp29'
print @SQL
exec(@SQL)
select * from #tbPA00H運行結果:
select pa_no,pa_peno into #tbPA00H from tempdb..tmp29(影響 2 個資料列)伺服器: 訊息 208,層級 16,狀態 1,行 5
Invalid object name '#tbPA00H'.都有兩條記錄被影響,按理說#tbPA00H臨時表是存在的,可是為何又不存在呢?
select pa_no,pa_peno into #tbPA00H from tempdb..tmp29
select * from #tbPA00H
可以得到我要的結果改用變量後卻不行
DECLARE @SQL varchar(100)
select @SQL='select pa_no,pa_peno into #tbPA00H from tempdb..tmp29'
print @SQL
exec(@SQL)
select * from #tbPA00H運行結果:
select pa_no,pa_peno into #tbPA00H from tempdb..tmp29(影響 2 個資料列)伺服器: 訊息 208,層級 16,狀態 1,行 5
Invalid object name '#tbPA00H'.都有兩條記錄被影響,按理說#tbPA00H臨時表是存在的,可是為何又不存在呢?
exec()会单独开一块内存空间,所以你的临时表出了EXEC就访问不了了
用##试试
但是如果只用局部變量這語句該怎樣改呢
-------------------------
那你把用到临时表的语句全部放到@s中去,
DECLARE @SQL varchar(100)
select @SQL='select pa_no,pa_peno into #tbPA00H from tempdb..tmp29 select * from #tbPA00H'
print @SQL
exec(@SQL)