How to access Temp Table in the stored procedure which is created in another stored procedure?

Question

I had an interview in which interviewer asked me a question that how can you access temp table in the stored procedure which is created in another stored procedure and that procedure does not drop temp table?

I answered him that you can access the temp table in a same session. He said but when you will do this:

Select * from #table

It will give an error because #table is not created in current SP. I said that you can access temp table in a same session and if both SP's are in same session then you can access that temp table. I did not try this but there will be some way to access it. He said yes you can access it but how? Try it at home.

I know that table created with #table is a temp table. It is only accessible in a same session. I am trying to access temp table created by other sp in a same session but i am unable to access it. Is there any way to do this?

Answer 1

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

Answer 2

you have to call the store procedure in which you want to use this temp table from the one which creates this temp table.