CODEWITHSUNDEEP
sql-serverscopetemp-tables
Kenny
Kenny

Reputation: 1982

"Level" scope in local temporary tables sql server

I am reading a book mentioning

Local temporary tables are visible throughout the level that created them, across batches, and in all inner levels of the call stacks. So if you create a temporary table in a specific level in your code and then execute a dynamic batch or a stored procedure, the inner batch can access the temporary table.

and

Table variables are not visible even across batches in the same level

From other sources, what I understand is that local temporary tables are visible to the session that created it and destroyed when the session closes.

So what is "level" and "inner levels of the call stacks" mean here?

Upvotes: 0

Views: 401

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Picture a stored procedure called A than, in turn, calls two stored procedures, B and then C1.

Each of these stored procedures creates a temporary table with the same name, prefixed (of course) by # (so, A creates #A) as their first action, before running any further code.

You execute the following code in query analyser:

CREATE TABLE #F (ID INT NOT NULL)
EXEC A
GO
EXEC A

Code in A can work with tables #F and #A.

Code in B can work with tables #F, #A and #B

Code in C can work with tables #F, #A and #C

Despite B and C being at the same "level" (they were both called from A), C cannot access the temp table that B created (it was destroyed when B exited).

1

CREATE PROCEDURE A
AS
    CREATE TABLE #A (ID INT NOT NULL)
    EXEC B
    EXEC C

Upvotes: 2

Related Questions