删除全局临时表 [英] Remove the global temp table

问题描述

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.

推荐答案

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

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

这篇关于删除全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！