x

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 '10 at 12:18 PM in Default

venkatreddy gravatar image

venkatreddy
562 28 29 32

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

2 answers: sort voted first

From MSDN BOL

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 '10 at 12:31 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 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;
go

create proc dbo.sp1 as

begin

    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

end;
go

create proc dbo.sp2 as

begin

    exec dbo.sp1;

    select top 2 * from ##t;

end;
go

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.

Oleg

more ▼

answered Apr 30 '10 at 12:31 PM

Oleg gravatar image

Oleg
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x402
x33

asked: Apr 30 '10 at 12:18 PM

Seen: 3698 times

Last Updated: Apr 30 '10 at 12:32 PM