本文介绍了删除全局临时表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE PROCEDURE MySP
(
@Type INT
)
DECLARE @strSQL AS VARCHAR(4000)
IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable
SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable';
EXECUTE(@strSQL)
如何从(@strSQL)中检索数据。
否则如何消除全局临时表。
How to retrive the data from (@strSQL).
Otherwise how to eliminate global temp table.
推荐答案
CREATE PROCEDURE MySP
(
@Type INT
)
as
begin
DECLARE @strSQL AS VARCHAR(4000)
IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable
SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable';
EXECUTE(@strSQL)
SELECT @strSQL = 'SELECT * FROM ##MyTmpTable';
EXECUTE (@strSQL)
end
Select * FROM ##MyTmpTable
如果要全局访问## MyTmpTable数据,请使用全局临时表,否则使用CTE(公用表表达式),它只能在查询窗口中使用它会减少数据库和sql server的内存和压力
If you want to access "##MyTmpTable" data globally use global temp table otherwise use CTE (Common Table Expressions) that can be useful in the query window only so that it will reduce the memory and stress on the database and sql server
CREATE PROCEDURE MySP
(
@Type INT
)
DECLARE @strSQL AS VARCHAR(4000)
IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable
SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable';
EXECUTE(@strSQL)
select * from ##MyTmpTable
如果有帮助请将其标记为答案
if it helps please mark it as answer
这篇关于删除全局临时表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!