sql - 临时存储过程范围

Question

SQL Server 支持临时表(本地和全局)。

使用动态 SQL(EXEC 或 dbo.sp_executesql)我们可以创建新的上下文，本地临时表仅在动态 SQL block 中可见，但在外部不可见。

-- Normal table
EXEC ('CREATE TABLE tab(i INT); INSERT INTO tab(i) VALUES (1)');
SELECT * FROM tab;

-- Global temporary table
EXEC ('CREATE TABLE ##tab(i INT); INSERT INTO ##tab(i) VALUES (2)');
SELECT * FROM ##tab;

-- Local temporary table
EXEC ('CREATE TABLE #tab(i INT); INSERT INTO #tab(i) VALUES (3)');
SELECT * FROM #tab;
-- Invalid object name '#tab'.

LiveDemo

现在让我们对存储过程进行同样的尝试:

-- Normal procedure
EXEC ('CREATE PROCEDURE my_proc AS SELECT 1 AS col;');
EXEC my_proc;

-- Global temporary procedure
EXEC ('CREATE PROCEDURE ##my_proc AS SELECT 2 AS col;');
EXEC ##my_proc;

-- Local temporary procedure
EXEC ('CREATE PROCEDURE #my_proc AS SELECT 3 AS col;');
EXEC #my_proc;

LiveDemo2

问题是为什么本地临时过程的行为不同并且它在 EXEC 之外可见？

Answer 1

之所以将它保留在 exec 之外，是因为 SQL Server 可以重用 proc 的查询计划。

一旦定义它的 session (通过 exec)关闭，proc 就会消失。

MS 非常小心地指出临时“表”(一个带有一个# 符号)是上下文本地的并且对调用程序不可见。

参见:您创建的任何表都对

中的 EXEC() 上下文可见

https://technet.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx