Local temporary table behavior in nested stored procedure with example
local temporary table scope remains only inside
the stored procedure, in which it is created. Any inner stored procedure can
access the local temporary table created in outer procedure. But any outer
stored procedure can not access the local temporary table created in inner
procedure.
Error you may get: Msg 208, Level 16, State 0, Procedure procedure1, Line 6
Invalid object name '#temp1'.
Error you may get: Msg 208, Level 16, State 0, Procedure procedure1, Line 6
Invalid object name '#temp1'.
Scenario 1: when one procedure calls another procedure and temp table is created in outer
procedure procedure1 and accessed by inner procedure procedure2
|
Scenario 2: when one procedure calls another procedure and temp table is created in inner
procedure procedure2 and accessed by outer procedure procedure1
|
create proc
procedure1
as
begin
create table
#temp1(name varchar(30),age int)
insert into
#temp1
select 'ram',20
union all
select 'shyam',23
union all
select 'anjum',28
exec procedure2
end
GO;
create proc
procedure2
as
begin
select *
from #temp1
end
GO;
|
create proc
procedure2
as
begin
create table
#temp1(name varchar(30),age int)
insert into
#temp1
select 'ram',20
union all
select 'shyam',23
union all
select 'anjum',28
end
GO;
create proc
procedure1
as
begin
exec procedure2
select *
from #temp1
end
GO;
|
 |
 |
As local temporary table scope is inside the
stored procedure, in which it is created.
Procedure2 is nested inside procedure1 , thus
procedure2 has access to temp table #temp1.
|
As local temporary table scope is only inside the
stored procedure in which it is created.
Temp table #temp1 is created in procedure2,
which is called by procedure1, thus any procedure inside procedure2 can
access that table, but when procedure 1 tries to access the table #temp1 ,
which is an outer procedure, doesn’t find the table #temp1
|
Please provide your feedback for the post, if you find this post useful.
No comments:
Post a Comment