Tempdb Que
-
Hi guys ..
we have one stored procedure running through front end application . That procedure creates temp table (#) but in procedure there is no DROP command for temp table at the end. So what would be happening with that temp table ?
That temp table resides always in tempdb or automatically dropped after SP finish execution?
Thanks...
-
It's scoped to the session, so it's dropped when the proc terminates.
-
Temp Objects are always tied to the current session. As soon as the session is closed, all the temp objects created for that session are destroyed from Temp DB
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
temp tables are tied to procs they are created , once it ends so does the temp tables ; also no exchange
in case of temp tables even though another proc is called within the procedure.
CREATE PROCEDURE test_hash_proc
AS
SELECT * FROM #test1
GO
CREATE PROCEDURE test_PROC
as
create table #test(a int)
insert into #test values(1)
EXEC test_hash_proc ---THIS WON'T WORK
GO
---Run the first proc
EXEC test_PROC
though the stored procedure got created , would never execute .
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
demonfox (7/27/2012)
temp tables are tied to procs they are created , once it ends so does the temp tables ; also no exchangein case of temp tables even though another proc is called within the procedure.
CREATE PROCEDURE test_hash_proc
AS
SELECT * FROM #test1
GO
CREATE PROCEDURE test_PROC
as
create table #test(a int)
insert into #test values(1)
EXEC test_hash_proc ---THIS WON'T WORK
GO
---Run the first proc
EXEC test_PROC
though the stored procedure got created , would never execute .
The code above won't work, but this will:
CREATE PROCEDURE test_hash_proc
AS
SELECT * FROM #test
GO
CREATE PROCEDURE test_PROC
as
create table #test(a int)
insert into #test values(1)
EXEC test_hash_proc
GO
---Run the first proc
EXEC test_PROC
-
My mistake ; better not put surprises ...
thanks for correcting ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply