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:
I was pretty sure only global temporary table could be accessed.
You prove me wrong.
Keyword here is :"in the current sesssion". The moment the session changes, the local temporary objects will no longer be accessible.
Thanks for the feedbacks
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