SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Honza Mensdorff / Execs and temporary tables / Latest Posts

RE: Execs and temporary tables

kalyani.k478 — Mon, 22 Oct 2012 23:35:22 GMT

I got it wrong :(

RE: Execs and temporary tables

honza.mf — Wed, 18 Jul 2012 12:56:35 GMT

[quote][b]L' Eomot Inversé (7/18/2012)[/b][hr]Nice easy question. But obviously batch 1 works, and batch 2 can't work (because the table is dropped by the exit from exec), and only one of the answers permits that; a different set of answer options could have made it necessary to look and see what the other batches did, which would perhaps have made it a better (although somewhat more tedious) question.[/quote]I preffered symetry over difficulty. Maybe next time.

RE: Execs and temporary tables

L' Eomot Inversé — Wed, 18 Jul 2012 10:08:40 GMT

Nice easy question. But obviously batch 1 works, and batch 2 can't work (because the table is dropped by the exit from exec), and only one of the answers permits that; a different set of answer options could have made it necessary to look and see what the other batches did, which would perhaps have made it a better (although somewhat more tedious) question.

RE: Execs and temporary tables

honza.mf — Wed, 18 Jul 2012 06:45:01 GMT

[quote][b]Mike Palecek (7/18/2012)[/b][hr]Very good question. I write code fairly often and it seems there is more to think about in SQL about scope than many programming languages. Either that or I just think about it more with programming.[/quote]Thank you. That's it.

RE: Execs and temporary tables

Mike Palecek — Wed, 18 Jul 2012 06:23:58 GMT

Very good question. I write code fairly often and it seems there is more to think about in SQL about scope than many programming languages. Either that or I just think about it more with programming.

RE: Execs and temporary tables

Lon-860191 — Tue, 10 Jul 2012 07:58:35 GMT

Good question, thanks.

RE: Execs and temporary tables

Danny Ocean — Mon, 09 Jul 2012 04:43:18 GMT

unfortunately, i select the wrong one. :doze:

RE: Execs and temporary tables

SQLPhil — Mon, 09 Jul 2012 01:36:19 GMT

[quote][b]David Harder (7/6/2012)[/b][hr]This question made up for yesterday's beating. Glad we ending the week with this one.[/quote]See I missed this on Friday so was still busy chewing some sour grapes! However, this week's starting out on a high note (despite the miserable British summer!) :cool:

RE: Execs and temporary tables

baabhu — Mon, 09 Jul 2012 01:15:30 GMT

Good Question !!!!!!.

RE: Execs and temporary tables

Koen Verbeeck — Mon, 09 Jul 2012 00:28:52 GMT

Thanks for the question.

RE: Execs and temporary tables

honza.mf — Sat, 07 Jul 2012 07:59:54 GMT

[quote][b]WayneS (7/6/2012)[/b][hr][quote][b]mtassin (7/6/2012)[/b][hr]Always nice to have an easy one on Friday.... [/quote]I concur... [b]but what is easy for one person isn't for another[/b].[/quote]I'm surprised, there are someone (9% now) who choosed the "Every batch throws an error" answer. Curious.

RE: Execs and temporary tables

honza.mf — Sat, 07 Jul 2012 07:57:48 GMT

[quote][b]bitbucket-25253 (7/6/2012)[/b][hr]Emphasis (bolding) added by this poster[quote][b]BrainDonor (7/6/2012)[/b][hr]That explanation requires expanding slightly, because there is more than one type of temporary table. [i]Local[/i] [b]temporary tables (prefixes with '#') go out of scope when the exec command completes.[/b] However, use '##' to create a [i]Global[/i] temporary table and the table can be accessed outside of the exec statement that created it.[/quote]At the completion of the command given in the QOD[code="sql"]exec ('create table #qotd2 (id int))'[/code] 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 [b]entire sequence of commands is contained within the scope of the exec command.[/b][code="sql"]exec ('create table #qotd2 (id int) alter table #qotd2 add i int insert into #qotd2 (i) values (2) select * from #qotd2 drop table #qotd2')[/code]The above then will return for the SELECT statement the values:[code="sql"]id iNULL 2 [/code][/quote]I must agree. It's the case of B#7.

RE: Execs and temporary tables

honza.mf — Sat, 07 Jul 2012 07:52:58 GMT

Thanks to everyone. Sorry, I missed the beggining of this discussion as I was out of civilization (holiday).

RE: Execs and temporary tables

honza.mf — Sat, 07 Jul 2012 07:51:16 GMT

[quote][b]Michael Poppers (7/6/2012)[/b][hr]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 :-).[/quote]Thanks.I wanted to have it uniform.

RE: Execs and temporary tables

honza.mf — Sat, 07 Jul 2012 07:49:40 GMT

[quote][b]BrainDonor (7/6/2012)[/b][hr]That explanation requires expanding slightly, because there is more than one type of temporary table. [i]Local[/i] temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a [i]Global[/i] temporary table and the table can be accessed outside of the exec statement that created it.[/quote]You are right. But with global temporary table this will be no fun.

RE: Execs and temporary tables

WayneS — Fri, 06 Jul 2012 12:50:32 GMT

[quote][b]mtassin (7/6/2012)[/b][hr]Always nice to have an easy one on Friday.... [/quote]I concur... but what is easy for one person isn't for another.

RE: Execs and temporary tables

Michael Poppers — Fri, 06 Jul 2012 11:50:03 GMT

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 :-).

RE: Execs and temporary tables

KWymore — Fri, 06 Jul 2012 09:58:19 GMT

Nice question to end the week on. Thanks!

RE: Execs and temporary tables

Revenant — Fri, 06 Jul 2012 09:12:25 GMT

[quote][b]SQLRNNR (7/6/2012)[/b][hr][quote][b]mtassin (7/6/2012)[/b][hr]Always nice to have an easy one on Friday.... [/quote]Yes it is.:-D[/quote]Count me in. :-)

RE: Execs and temporary tables

EL Jerry — Fri, 06 Jul 2012 09:08:01 GMT

Nice question, thanks for the back to basics.Happy weekend, everyone."El" Jerry.

RE: Execs and temporary tables

SQLRNNR — Fri, 06 Jul 2012 09:03:56 GMT

[quote][b]mtassin (7/6/2012)[/b][hr]Always nice to have an easy one on Friday.... [/quote]Yes it is.:-D

RE: Execs and temporary tables

mtassin — Fri, 06 Jul 2012 08:30:51 GMT

Always nice to have an easy one on Friday....

RE: Execs and temporary tables

bitbucket-25253 — Fri, 06 Jul 2012 08:12:13 GMT

Emphasis (bolding) added by this poster[quote][b]BrainDonor (7/6/2012)[/b][hr]That explanation requires expanding slightly, because there is more than one type of temporary table. [i]Local[/i] [b]temporary tables (prefixes with '#') go out of scope when the exec command completes.[/b] However, use '##' to create a [i]Global[/i] temporary table and the table can be accessed outside of the exec statement that created it.[/quote]At the completion of the command given in the QOD[code="sql"]exec ('create table #qotd2 (id int))'[/code] 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 [b]entire sequence of commands is contained within the scope of the exec command.[/b][code="sql"]exec ('create table #qotd2 (id int) alter table #qotd2 add i int insert into #qotd2 (i) values (2) select * from #qotd2 drop table #qotd2')[/code]The above then will return for the SELECT statement the values:[code="sql"]id iNULL 2 [/code]

RE: Execs and temporary tables

Cliff Jones — Fri, 06 Jul 2012 07:42:37 GMT

[quote][b]David Harder (7/6/2012)[/b][hr]This question made up for yesterday's beating. Glad we ending the week with this one.[/quote]Yes, I agree.

RE: Execs and temporary tables

David Harder — Fri, 06 Jul 2012 07:28:59 GMT

This question made up for yesterday's beating. Glad we ending the week with this one.

RE: Execs and temporary tables

Dana Medley — Fri, 06 Jul 2012 07:01:49 GMT

Great question and a nice way to end the week. :-)

RE: Execs and temporary tables

Thomas Abraham — Fri, 06 Jul 2012 06:00:41 GMT

[quote][b]BrainDonor (7/6/2012)[/b][hr]That explanation requires expanding slightly, because there is more than one type of temporary table. [i]Local[/i] temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a [i]Global[/i] temporary table and the table can be accessed outside of the exec statement that created it.[/quote]Thanks for the amplification.And thanks to OP for the interesting question.

RE: Execs and temporary tables

Duncan Pryde — Fri, 06 Jul 2012 04:47:40 GMT

Good question, thanks.

RE: Execs and temporary tables

skanker — Fri, 06 Jul 2012 01:45:22 GMT

My gut instinct on this was right. Unfortunately I did not listen to this and chose incorrectly. Doh!!:hehe:

RE: Execs and temporary tables

Stewart "Arturius" Campbell — Fri, 06 Jul 2012 01:28:57 GMT

Good back-to-basics question, thanks Honza.

RE: Execs and temporary tables

BrainDonor — Fri, 06 Jul 2012 01:11:37 GMT

That explanation requires expanding slightly, because there is more than one type of temporary table. [i]Local[/i] temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a [i]Global[/i] temporary table and the table can be accessed outside of the exec statement that created it.

Execs and temporary tables

honza.mf — Thu, 05 Jul 2012 21:40:01 GMT

Comments posted to this topic are about the item [B]Execs and temporary tables[/B]