Temp Table Documentation Confusion

Question

I'm having trouble understanding some of the Temp table  documentation  in the SQL BOL.

To quote

"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger is called. However, if a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:"

 

So statement 1 is:

"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger is called. However, if a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against."

This tells me that a temp table created in a stored procedure can get confused with another temp table with the same name.

But then statement 2 is :

Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example: etc...

So this tells me that a temp table created in a stored procedure always references the one created in that proc.

Under what circumstances is statement 1 true - when can a stored procedure become confused between a temp table created in the proc and another existing temp table.

A small code example of where the problem could occur would be appreciated.

Thanks

 

---

JS

Answer 1

Hi JS,

So statement 1 is:

"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger is called. However, if a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against."

This tells me that a temp table created in a stored procedure can get confused with another temp table with the same name.

This statement means that if you have a stored procedure and a trigger, assuming the stored procedure is called by the trigger, it will be allowed to have a same named temporary table in both the stored procedure and trigger. But if there is a query in the trigger with this temporary table, it cannot define which temporary table is actually referenced. Anyway, the query will reference one temporary table and no error occurs.

USE tempdb 
GO

CREATE PROCEDURE dbo.Test1
AS
    CREATE TABLE #t(x INT PRIMARY KEY);
    INSERT INTO #t VALUES (2);
    SELECT * FROM #t;
GO

CREATE TABLE TB(id INT)
GO

CREATE TRIGGER reminder1
ON TB
AFTER INSERT, UPDATE 
AS 
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
EXEC dbo.Test1
SELECT * FROM #T
GO

INSERT INTO TB SELECT 1

However, you cannot create two temporary tables with the same by one stored procedure.

USE tempdb 
GO

CREATE PROCEDURE dbo.Test
AS
    CREATE TABLE #t(x INT PRIMARY KEY);
    INSERT INTO #t VALUES (2);
    SELECT * FROM #t;
    CREATE TABLE #t(y INT PRIMARY KEY);
    INSERT INTO #t VALUES (2);
    SELECT * FROM #t;
GO
--error 
Msg 2714, Level 16, State 1, Procedure Test, Line 6
There is already an object named '#t' in the database.

But then statement 2 is :

Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example: etc...

So this tells me that a temp table created in a stored procedure always references the one created in that proc.

Under what circumstances is statement 1 true - when can a stored procedure become confused between a temp table created in the proc and another existing temp table.

This scenario is just like the one above. For nested stored procedures, assuming a stored procedure is called by another stored procedure, they can also create a temporary table with a same table name respectively. However, for the stored procedure which is called by another, if it wants to modify this temporary table with the same name as another, this table must have the same structure, with the same column names, as the table created in the calling procedure, otherwise, an error will occur. The following example is about different column names on temporary tables with same table name.

USE tempdb 
GO

CREATE PROCEDURE dbo.Test3
AS
    CREATE TABLE #t(x INT PRIMARY KEY);
    INSERT INTO #t VALUES (2);
    UPDATE #t SET x=0
GO

CREATE PROCEDURE dbo.Test2
AS    
CREATE TABLE #t(Y INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
SELECT * FROM #t;
EXEC Test3;
GO

EXEC dbo.Test2
GO
--ERROR 
Msg 207, Level 16, State 1, Procedure Test3, Line 5
Invalid column name 'x'.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support