Can we use the temporary table created in one procedure from another procedure?
The answer depends on how you call the procedure Take this example Create a temporary table in a procedure
create procedure proc_1
as
create table #t(i int)
insert into #t select 10
select i from #t
Now create another procedure
create procedure proc_2
as
exec proc_1
Now execute proc_2
EXEC proc_2
You get the result 10 which means that it calls the procedure proc_1 that creates temporary table #t
See what happens when you try to insert into #t from proc_2
Alter the procedure
alter procedure proc_2
as
exec proc_1
insert into #t select 100
exec proc_1
When you execute proc_2
EXEC proc_2
You get an error saying
Msg 208, Level 16, State 0, Procedure proc_2, Line 4
Invalid object name '#t'.
It is becuase when proc_2 calls proc_1, temporary table is created but when the control returns to calling procedure the scope is lost
The proper way of doing this is to modify the procedures like below
alter procedure proc_1
as
create table #t(i int)
exec proc_2
select i from #t
go
alter procedure proc_2
as
insert into #t select 10
Now execute proc_1
EXEC proc_1
The result is 10. Proc_1 first creates the temporary table #t and calls proc_2. Becuase the execution of the proc_1 is not yet completed, the scope is carried forward to called procedure proc_2 that inserts a value 10 to #t. When the control returns to proc_1 it execute rest of the code and displays the result correctly.
So when you want to access temporary procedure across many procedures, always creates the temporary table at the Calling procedure and access it at Called Procedures