Temporary table created in parent SP persists in child SP

Question

Temporary table created in parent SP persists in child SP

C Hrushi

SSC-Addicted

Points: 469
More actions
September 17, 2013 at 12:42 am

#282542

Many of you may know this but I found below observation today and though of posting it:
I have creted One procedure test1 and created temp table in it. Executed another procedure test2 and fetch temp table created in
parent procedure test 1. it ran fine.
**********************************************************************************************
create procedure dbo.test2
as
begin
select * from #temptabletest
end
create procedure dbo.test1
as
begin
create table #temptabletest
(
col1 int
)
insert into #temptabletest values(1)
insert into #temptabletest values(2)
insert into #temptabletest values(3)
exec dbo.test2
end
Exec dbo.test1

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 1

Hi

Temporary tables are limited to the session. A local temporary table that is created in a stored procedure is dropped when the procedure ends. You execute proc test2 when test1 is still "alive".

Br.

Mike

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

C Hrushi (9/17/2013)
Many of you may know this but I found below observation today and though of posting it:

Thank you for your thoughtfulness. This is actually a good subject that has many different aspects to it. For example, it could be used to pass 100,000 "parameters" to a stored procedure without using Table Valued Parameters, XML, or a whole gambit of other things. If you care to get your feet wet, do some research and write a short article about it. Submit the article to this site. Just as a thought for a "first timer", you might want to have a couple of people review your article before submitting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)