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 Thursday, July 5, 2012 9:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,393, Visits: 1,315
Comments posted to this topic are about the item Execs and temporary tables



See, understand, learn, try, use efficient
© Dr.Plch
Post #1325827
Posted Friday, July 6, 2012 1:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 1,472, Visits: 8,402
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.


BrainDonor
Linkedin
Blog Site
Post #1325875
Posted Friday, July 6, 2012 1:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 4,050, Visits: 5,366
Good back-to-basics question, thanks Honza.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1325878
Posted Friday, July 6, 2012 1:45 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: Yesterday @ 7:04 AM
Points: 901, Visits: 1,281
My gut instinct on this was right. Unfortunately I did not listen to this and chose incorrectly. Doh!!
Post #1325884
Posted Friday, July 6, 2012 4:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Good question, thanks.
Post #1325979
Posted Friday, July 6, 2012 6:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:44 AM
Points: 1,860, Visits: 2,187
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.


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1326022
Posted Friday, July 6, 2012 7:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 1,931, Visits: 1,446
Great question and a nice way to end the week.



Everything is awesome!
Post #1326062
Posted Friday, July 6, 2012 7:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 4,058, Visits: 1,213
This question made up for yesterday's beating. Glad we ending the week with this one.
Post #1326081
Posted Friday, July 6, 2012 7:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:37 AM
Points: 3,969, Visits: 3,646
David Harder (7/6/2012)
This question made up for yesterday's beating. Glad we ending the week with this one.

Yes, I agree.
Post #1326092
Posted Friday, July 6, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1326112
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse