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?

Solution 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

Solution 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.

Comments

[Damien_The_Unbeliever]

Temporary Tables: "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table."

[umer]

@TheGmaeiswar thanks. I tested and result was same as you said. Thanks it's very helpful to me.

[umer]

Thanks for you answer.

[TheGameiswar]

Glad it helped you