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: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!
July 6, 2012 at 11:50 am
Thanks for the question! but the set of possible answers was "too easy": once I saw that B#2 was going to error out while B#3 was not, I didn't look at the subsequent Bs :-).
July 6, 2012 at 12:50 pm
mtassin (7/6/2012)
Always nice to have an easy one on Friday....
I concur... but what is easy for one person isn't for another.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply