July 5, 2012 at 9:40 pm
Comments posted to this topic are about the item Execs and temporary tables
July 6, 2012 at 1:11 am
That explanation requires expanding slightly, because there is more than one type of temporary table.
Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.
July 6, 2012 at 1:28 am
This was removed by the editor as SPAM
July 6, 2012 at 1:45 am
My gut instinct on this was right. Unfortunately I did not listen to this and chose incorrectly. Doh!!:hehe:
July 6, 2012 at 4:47 am
Good question, thanks.
July 6, 2012 at 6:00 am
BrainDonor (7/6/2012)
That explanation requires expanding slightly, because there is more than one type of temporary table.Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.
Thanks for the amplification.
And thanks to OP for the interesting question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 6, 2012 at 7:01 am
Great question and a nice way to end the week. 🙂
July 6, 2012 at 7:29 am
This question made up for yesterday's beating. Glad we ending the week with this one.
July 6, 2012 at 7:42 am
David Harder (7/6/2012)
This question made up for yesterday's beating. Glad we ending the week with this one.
Yes, I agree.
July 6, 2012 at 8:12 am
Emphasis (bolding) added by this poster
BrainDonor (7/6/2012)
That explanation requires expanding slightly, because there is more than one type of temporary table.Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.
At the completion of the command given in the QOD
exec ('create table #qotd2 (id int))' follows the above since the exec command completes, that is #qotd2 has gone out of scope.
To illustrate, the code below has been modified so that the entire sequence of commands is contained within the scope of the exec command.
exec ('create table #qotd2 (id int)
alter table #qotd2 add i int
insert into #qotd2 (i) values (2)
select * from #qotd2
drop table #qotd2')
The above then will return for the SELECT statement the values:
id i
NULL 2
July 6, 2012 at 8:30 am
Always nice to have an easy one on Friday....
July 6, 2012 at 9:03 am
mtassin (7/6/2012)
Always nice to have an easy one on Friday....
Yes it is.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 6, 2012 at 9:08 am
July 6, 2012 at 9:12 am
SQLRNNR (7/6/2012)
mtassin (7/6/2012)
Always nice to have an easy one on Friday....Yes it is.:-D
Count me in. 🙂
July 6, 2012 at 9:58 am
Nice question to end the week on. Thanks!
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply