Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Execs and temporary tables Expand / Collapse
Author
Message
Posted Saturday, July 7, 2012 7:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:18 AM
Points: 1,369, Visits: 1,314
bitbucket-25253 (7/6/2012)
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


I must agree. It's the case of B#7.




See, understand, learn, try, use efficient
© Dr.Plch
Post #1326425
Posted Saturday, July 7, 2012 7:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:18 AM
Points: 1,369, Visits: 1,314
WayneS (7/6/2012)
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.

I'm surprised, there are someone (9% now) who choosed the "Every batch throws an error" answer. Curious.




See, understand, learn, try, use efficient
© Dr.Plch
Post #1326426
Posted Monday, July 9, 2012 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 13,730, Visits: 10,683
Thanks for the question.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1326675
Posted Monday, July 9, 2012 1:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 1,287, Visits: 1,120
Good Question !!!!!!.
Post #1326685
Posted Monday, July 9, 2012 1:36 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:46 AM
Points: 1,442, Visits: 723
David Harder (7/6/2012)
This question made up for yesterday's beating. Glad we ending the week with this one.


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!)
Post #1326694
Posted Monday, July 9, 2012 4:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
unfortunately, i select the wrong one.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1326749
Posted Tuesday, July 10, 2012 7:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:51 AM
Points: 1,426, Visits: 254
Good question, thanks.
Post #1327568
Posted Wednesday, July 18, 2012 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:35 AM
Points: 845, Visits: 732
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.
Post #1331412
Posted Wednesday, July 18, 2012 6:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:18 AM
Points: 1,369, Visits: 1,314
Mike Palecek (7/18/2012)
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.

Thank you. That's it.




See, understand, learn, try, use efficient
© Dr.Plch
Post #1331426
Posted Wednesday, July 18, 2012 10:08 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 8,830, Visits: 9,388
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.


Tom
Post #1331602
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse