Temporary Table in Stored Procedure -

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.