How to access a Temp table in a stored procedure that is created in another stored procedure?

Question

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

I told him that you can access the temp table in one session. He said, but when you do this:

Select * from #table

This will give an error because #table is not created in the current SP. I said that you can access the temp table in one session, and if both SPs are in the same session, you can access this temporary table. I have not tried this, but there will be access to it. He said yes, you can access it, but how? Try it at home.

I know that a table created with #table is a temporary table. It is available in only one session. I am trying to access the temp table created by other sp in one session, but I cannot access it. Is there any way to do this?

Answer 1

Based on the comments of Damien.

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 running the test gives

innertest1 

1

innertest2

1

The table cannot reference a process called the stored procedure that created the table

this is obvious if usp_innertest1 creates a temporary table, a test cannot be received for it (the main call process)

, ,

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

, , , .