In this blog we will see the scope of temporary table in stored procedure. Temporary tables or a caller stored procedure can be access inside a child procedure but the reverse doesn’t hold true.
Let us first see if a parent stored procedure temporary table of can be accessed inside the called stored procedure.
if object_id('ParentProc','P') is not null drop procedure ParentProc; go create procedure ParentProc as begin set nocount on; if OBJECT_ID('tempdb..#tempTable') is not null drop table #tempTable; create table #tempTable ( id int, firstName nvarchar(50), lastName nvarchar(50) ); insert into #tempTable (id,firstName,lastName) select BusinessEntityID,FirstName,LastName from Person.Person; exec ChildProc; end go if object_id('ChildProc','P') is not null drop procedure ChildProc; go create procedure ChildProc as select * from #tempTable; go --execute the parent proc exec ParentProc; |
This proves that we could access the callers stored procedure temporary tables inside the called stored procedure.
Now let us try the reverse the process and create the temp table in the child procedure and try to access that inside the caller procedure
if object_id('ParentProc','P') is not null drop procedure ParentProc; go create procedure ParentProc as begin set nocount on; exec ChildProc; --if OBJECT_ID('tempdb..#tempTable') is not null select * from #tempTable; end go if object_id('ChildProc','P') is not null drop procedure ChildProc; go create procedure ChildProc as begin set nocount on; if OBJECT_ID('tempdb..#tempTable') is not null drop table #tempTable; create table #tempTable ( id int, firstName nvarchar(50), lastName nvarchar(50) ); insert into #tempTable (id,firstName,lastName) select BusinessEntityID,FirstName,LastName from Person.Person; end go --execute the parent proc exec ParentProc; |
This proves that we could not access a child stored procedure’s temporary table inside the caller stored procedure.
One way we could make the above case to work is by using global temporary tables. Global temporary tables.
if object_id('ParentProc','P') is not null drop procedure ParentProc; go create procedure ParentProc as begin set nocount on; exec ChildProc; --if OBJECT_ID('tempdb..##tempTable') is not null select * from ##tempTable; end go if object_id('ChildProc','P') is not null drop procedure ChildProc; go create procedure ChildProc as begin set nocount on; if OBJECT_ID('tempdb..##tempTable') is not null drop table ##tempTable; create table ##tempTable ( id int, firstName nvarchar(50), lastName nvarchar(50) ); insert into ##tempTable (id,firstName,lastName) select BusinessEntityID,FirstName,LastName from Person.Person; end go --execute the parent proc exec ParentProc; |
As long as the current session where we generate a global table is not closed any user in the database can access it.