Child sProc cannot reference a Local temp table created in parent sProc

Question

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.

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.

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

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).

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.

Answer 1

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.

try this from a query window in management studio:

create these two procedures:

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

then run them:

exec caller

This is what I get on SQL Server 2005:

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

(2 row(s) affected)

Answer 2

We do this now (on 2000, 2005, and 2008) exactly as you describe without having to change local to global temp tables.