How to access Temp Table in the stored procedure which is created in another stored procedure?
Building on Damien comments..
The table can be referenced by any nested stored procedures executed by the stored procedure that created the table
create proc usp_innertest1
as
begin
select n as innertest1 from #test
end
create proc usp_innertest2
as
begin
select n as innertest2 from #test
end
create proc dbo.test
as
begin
select top 1* into #test from numbers
exec usp_innertest1
exec usp_innertest2
end
now Executing test gives
innertest1
1
innertest2
1
The table cannot be referenced by the process which called the stored procedure that created the table
this is obvious,if usp_innertest1 creates a temp table,it can't be accessed by test(main calling process)
There are also global temporary tables which reside till all the references are closed
---connection1
select top 1 * into ##test from numbers
--now open new connection(connection 2) and do below
begin tran
update ##test
set id=1
--now close connection1
-- now go to connection 2
select * from ##test
you can access this table until you committed it
commit