且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

删除全局临时表

更新时间:2022-12-22 13:12:08

尝试执行下面给出的方式 -



Try executing the given below way -

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


执行@strsql后可以查询全局临时表



You can query down global temp table after executing @strsql

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


hi,



你可以通过添加select来实现程序结束时的声明如下:






You can do it by adding select statement at end of procedure as below


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