存储过程中exec内的临时表的作用域规则是什么? [英] What's the scoping rule for temporary tables within exec within stored procedures?

问题描述

比较以下存储过程:

CREATE PROCEDURE testProc1
AS
    SELECT * INTO #temp FROM information_schema.tables
    SELECT * FROM #temp
GO

CREATE PROCEDURE testProc2
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables')
    SELECT * FROM #temp
GO

现在，如果我运行testProc1，它将起作用，并且#temp似乎仅在该呼叫期间存在.但是，testProc2似乎根本不起作用，因为我收到了Invalid object name '#temp'错误消息.

Now, if I run testProc1, it works, and #temp seems to only exist for the duration of that call. However, testProc2 doesn't seem to work at all, since I get an Invalid object name '#temp' error message instead.

为什么有区别，并且如果源表名称是存储过程的参数并且可以具有任意结构，我该如何在SELECT * INTO中使用临时表?

Why the distinction, and how can I use a temp table to SELECT * INTO if the source table name is a parameter to the stored procedure and can have arbitrary structure?

请注意，我正在使用Microsoft SQL Server 2005.

Note that I'm using Microsoft SQL Server 2005.

推荐答案

来自BOL:

本地临时表可见 仅在当前会话中... ... 临时表是自动的 当它们超出范围时下降， 除非使用DROP明确删除 表格

Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE

您在第一个步骤和第二个步骤之间的区别在于，在第一个步骤中，表的定义与选择它的范围相同；在第二种情况下，EXEC()在其自己的范围内创建表，因此选择在这种情况下会失败...

The distinction between your first and second procedures is that in the first, the table is defined in the same scope that it is selected from; in the second, the EXEC() creates the table in its own scope, so the select fails in this case...

但是，请注意以下工作正常:

However, note that the following works just fine:

CREATE PROCEDURE [dbo].[testProc3]
AS
    SELECT * INTO #temp FROM information_schema.tables
    EXEC('SELECT * FROM #temp')
GO

它之所以有效，是因为EXEC的范围是存储过程的范围的子级.在父级作用域中创建表时，该表也适用于任何子级.

And it works because the scope of EXEC is a child of the scope of the stored procedure. When the table is created in the parent scope, it also exists for any of the children.

为了给您一个好的解决方案，我们需要更多地了解您要解决的问题...但是，如果您只需要从创建的表中进行选择，请在子作用域中执行选择效果很好:

To give you a good solution, we'd need to know more about the problem that you're trying to solve... but, if you simply need to select from the created table, performing the select in the child scope works just fine:

CREATE PROCEDURE [dbo].[testProc4]
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables; SELECT * FROM #temp')
GO

这篇关于存储过程中exec内的临时表的作用域规则是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！