Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Scope of temporary tables across procedures

Apr 20 2010 5:19AM by Madhivanan   

Can we use the temporary table created in one procedure from another procedure?

The answer depends on how you call the procedure Take this example Create a temporary table in a procedure

create procedure proc_1
as
create table #t(i int)
insert into #t select 10
select i from #t

Now create another procedure

create procedure proc_2
as
exec proc_1

Now execute proc_2

EXEC proc_2

You get the result 10 which means that it calls the procedure proc_1 that creates temporary table #t

See what happens when you try to insert into #t from proc_2

Alter the procedure

alter procedure proc_2
as
exec proc_1
insert into #t select 100
exec proc_1

When you execute proc_2

EXEC proc_2

You get an error saying

Msg 208, Level 16, State 0, Procedure proc_2, Line 4
Invalid object name '#t'.

It is becuase when proc_2 calls proc_1, temporary table is created but when the control returns to calling procedure the scope is lost

The proper way of doing this is to modify the procedures like below

alter procedure proc_1
as
create table #t(i int)
exec proc_2
select i from #t

go

alter procedure proc_2
as
insert into #t select 10

Now execute proc_1

EXEC proc_1

The result is 10. Proc_1 first creates the temporary table #t and calls proc_2. Becuase the execution of the proc_1 is not yet completed, the scope is carried forward to called procedure proc_2 that inserts a value 10 to #t. When the control returns to proc_1 it execute rest of the code and displays the result correctly.

So when you want to access temporary procedure across many procedures, always creates the temporary table at the Calling procedure and access it at Called Procedures

Tags: t-sql, sql_server, temporary_table,


Madhivanan
3 · 40% · 12947
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • But if #t is created again in child proc, then SQL Server engine treats both as different #table.

    alter procedure proc1 as create table #t(i int) --insert into #t select 5 exec proc2 select i from #t

    go

    alter procedure proc_2 as create table #t(i int) insert into #t select 10

    go exec proc1 ==========> results no record.Uncommenting insert statement in proc1 results 5

    Ref: http://msdn.microsoft.com/en-us/library/ms174979.aspx

    commented on Nov 28 2012 6:39AM
    Gopi
    293 · 0% · 147
  • Yes because thats how temporary table works in SQL Server

    commented on Nov 28 2012 7:08AM
    Madhivanan
    3 · 40% · 12947

Your Comment


Sign Up or Login to post a comment.

"Scope of temporary tables across procedures" rated 5 out of 5 by 4 readers
Scope of temporary tables across procedures , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]