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

Temporary table and dynamic sql

Apr 25 2012 12:00AM by Madhivanan   

One of my collegues told me that while we cannot use a temporary table created inside dynamic sql out of it, in a similar way it is not possible inside dynamic sql to access a temporary table created out of dynamic sql. How many of you think this is true? I immediately showed him the following code to prove him wrong.

create table #t (i int)
GO
exec ('insert into #t(i) select 100')
GO
select i from #t

The result is 100. As you see it is possible to access the temporary table via dynamic sql in the current session. Ok now the question is "How do I know what objects I can access via dynamic sql?". You can access all the objects that you see from sys.objects. You need to aware of this when using temporary table and dynamic sql.

You may also be interested to read this post Scope of temporary tables across procedures

Tags: 


Madhivanan
3 · 40% · 12936
3 Readers said this post helped them to refresh memory
Adam Tokarski said this post helped them to refresh memory on 4/25/2012 8:12:00 AM
Profile
Nakul Vachhrajani said this post helped them to refresh memory on 4/25/2012 12:43:00 PM
Profile · Blog · Facebook · Twitter
Olga Medvedeva said this post helped them to refresh memory on 4/25/2012 9:43:00 PM
Profile · Blog · Twitter
9
 
0
Lifesaver
 
3
Refreshed
 
 
0
Incorrect



Submit

4  Comments  

  • I was pretty sure only global temporary table could be accessed.

    You prove me wrong.

    commented on Apr 25 2012 8:38AM
    Sergejack
    41 · 4% · 1395
  • Keyword here is :"in the current sesssion". The moment the session changes, the local temporary objects will no longer be accessible.

    commented on Apr 25 2012 12:43PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Thanks for the feedbacks

    commented on Apr 26 2012 1:48AM
    Madhivanan
    3 · 40% · 12936
  • We can NOT access dynamically created temp table in static (without dynamic sql) query. Here is the proof:

    exec ('create table #t (i int) ; insert into #t(i) select 100')
    select  * from #t
    
    commented on Nov 22 2012 10:36PM
    imtiyaz
    3071 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Temporary table and dynamic sql" rated 5 out of 5 by 9 readers
Temporary table and dynamic sql , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]