9

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?

Umer Waheed
  • 4,044
  • 7
  • 41
  • 62
  • 3
    [Temporary Tables](https://msdn.microsoft.com/en-us/library/ms174979.aspx): "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." – Damien_The_Unbeliever Mar 22 '17 at 07:33
  • [Local and global temporary tables in SQL Server](http://stackoverflow.com/a/2921091/6468577) – neer Mar 22 '17 at 07:46

4 Answers4

10

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
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
3

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

ProgrammerBoy
  • 876
  • 6
  • 19
1

If you want to use for global the temp table like...

select Email='temp@sample.com',[Name]='TemplName'
into ##testTempTable

in another scope even in view or stor procedure, use to select possible columns like..

select *from ##testTempTable
Rejwanul Reja
  • 1,339
  • 1
  • 17
  • 19
0
select distinct object_name(id) 
from syscomments 
where text like '%#tmp_table_name%' 
order by object_name(id)

This will return a list of any stored procedures that use that specific text of the temp table

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user12059048
  • 1