temp table and stored procedure related

I have a stored procedure sp1. Inside this sp I used a temp table t1. I have another stored procedure sp2. Can I call sp1 from sp2? What is the scope of t1 in this case.

I faced this question in an interview. Thanks in advance.

more ▼

asked Apr 30, 2010 at 12:18 PM in Default

venkatreddy gravatar image

562 28 29 32

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first


Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

  • A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

  • All other local temporary tables are dropped automatically at the end of the current session.

  • Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

more ▼

answered Apr 30, 2010 at 12:31 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

The scope of the temp table depends on whether it was created as local (name begins with #) or as global (name begins with ##). Local temp table is visible to the connection created it, while the global one is visible to all connections buu the moment the last connection using the global temp table is closed the table goes out of scope (gets dropped). In your case, yes you can have sp2 calling sp1 which creates temp table and after the sp1 bails out your sp2 can still continue talking to temp table if it is global, for example:

use AdventureWorks;

create proc dbo.sp1 as


    create table ##t (col1 int);

    insert into ##t
    select top 10 
        row_number() over (order by [object_id]) col1 
        from sys.objects;

    select * from ##t


create proc dbo.sp2 as


    exec dbo.sp1;

    select top 2 * from ##t;


The above allows exec dbo.sp2; returning 2 sets, but if you replace the ## with # then exec dbo.sp2 will bark as #t will not be available for it was local and its scope was limited to sp1 proc.


more ▼

answered Apr 30, 2010 at 12:31 PM

Oleg gravatar image

15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 30, 2010 at 12:18 PM

Seen: 4016 times

Last Updated: Apr 30, 2010 at 12:32 PM