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.
asked
temp table and stored procedure related
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.
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
