子sProc无法引用在父sProc中创建的本地临时表 [英] Child sProc cannot reference a Local temp table created in parent sProc

问题描述

在生产SQL2000实例上，我们有一个包含数百个存储过程的数据库，其中许多存储过程使用在代码中早"创建#TEMP表的技术，然后各种内部存储过程被此父sProc执行.在SQL2000中，内部或子" sProc没问题，可以插入#TEMP或从#TEMP中选择数据.简而言之，我假设他们都可以引用此#TEMP，因为他们使用相同的连接.

On our production SQL2000 instance, we have a database with hundreds of stored procedures, many of which use a technique of creating a #TEMP table "early" on in the code and then various inner stored procedures get EXECUTEd by this parent sProc. In SQL2000, the inner or "child" sProc have no problem INSERTing into #TEMP or SELECTing data from #TEMP. In short, I assume they can all refer to this #TEMP because they use the same connection.

在使用SQL2008进行测试时，我发现了两种不同行为的表现形式.首先，在设计时，新的智能"功能在子sProc的Management Studio EDIT中抱怨#TEMP是无效的对象名称".但更糟糕的是，在执行时，被调用的父sProc在嵌套子sProc中失败.

In testing with SQL2008, I find 2 manifestations of different behavior. First, at design time, the new "intellisense" feature is complaining in Management Studio EDIT of the child sProc that #TEMP is an "invalid object name". But worse is that at execution time, the invoked parent sProc fails inside the nested child sProc.

有人建议解决方案是更改为## TEMP，这显然是一个全局临时表，可以从不同的连接中引用该表.

Someone suggested that the solution is to change to ##TEMP which is apparently a global temporary table which can be referenced from different connections.

从从网络应用程序中调用这些sProcs的工作量(查找所有问题点)以及可能的/可能的令人讨厌的效果来看，这似乎太过激烈了.

That seems too drastic a proposal both from the amount of work to chase down all the problem spots as well as possible/probable nasty effects when these sProcs are invoked from web applications (i.e. multiuser issues).

这确实是SQL2005或SQL2008中与#TEMP(本地临时表)有关的行为更改吗?我们跳过了2005年，但我想更精确地了解为什么会这样，然后再尝试破解所需的修复程序.谢谢.

Is this indeed a change in behavior in SQL2005 or SQL2008 regarding #TEMP (local temp tables)? We skipped 2005 but I'd like to learn more precisely why this is occuring before I go off and try to hack out the needed fixes. Thanks.

推荐答案

在存储过程之间共享临时表是一个不错的功能:

sharing a temp table between stored procedures is a nice feature to use: http://www.sommarskog.se/share_data.html#temptables, I'm surprised that it isn't working for you. Perhaps you should try a very simple example and see if that will work. Then if that works start looking at other reasons.

在Management Studio的查询窗口中尝试以下操作:

try this from a query window in management studio:

创建这两个过程:

CREATE PROCEDURE called_procedure 
(@par1 int, @par2 char(5))
AS
INSERT INTO  #tmp VALUES (@par1,@par2)
GO

CREATE PROCEDURE caller
AS

CREATE TABLE #tmp (col1 int     NOT NULL
                  ,col2 char(5) NULL
                  )
EXEC called_procedure 1, 'AAA'
EXEC called_procedure 2, 'BBB'

SELECT * FROM #tmp
GO

然后运行它们:

exec caller

这是我在SQL Server 2005上得到的:

This is what I get on SQL Server 2005:

col1        col2
----------- -----
1           AAA  
2           BBB  

(2 row(s) affected)

这篇关于子sProc无法引用在父sProc中创建的本地临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！