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 ««123»»

Counts Puzzle Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2008 11:17 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
You forgot to put drops for you temp tables (#data, #category) at the end of the code block.


Post #559061
Posted Tuesday, August 26, 2008 11:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:27 PM
Points: 350, Visits: 229
YeshuaAgapao (8/26/2008)
You forgot to put drops for you temp tables (#data, #category) at the end of the code block.


That doesn't affect the end result tho ;).
Post #559086
Posted Tuesday, August 26, 2008 12:26 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.

Better yet, re-write it so it uses table variables.



Post #559109
Posted Wednesday, August 27, 2008 4:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Good Question...


Post #559484
Posted Wednesday, August 27, 2008 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
YeshuaAgapao (8/26/2008)
It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.

Better yet, re-write it so it uses table variables.

I think it is perfectly valid not to drop temp tables explicitly, since they go out of scope automatically.
I am also curious to know why you favor table variables, especially for this example.


Best Regards,
Chris Büttner
Post #559581
Posted Wednesday, August 27, 2008 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:27 PM
Points: 350, Visits: 229
Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).
Post #559589
Posted Wednesday, August 27, 2008 7:36 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 453, Visits: 102
skyline666 (8/27/2008)
Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).

That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.
Post #559630
Posted Wednesday, August 27, 2008 7:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:27 PM
Points: 350, Visits: 229
Aleksandr Furman (8/27/2008)
skyline666 (8/27/2008)
Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).

That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.


Yes, sorry that is what I meant but didn't say very clearly!
Post #559637
Posted Thursday, August 28, 2008 12:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
Aleksandr Furman (8/27/2008)
skyline666 (8/27/2008)
Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).

That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.


Hi Aleksandr,

Local temporary tables are dropped when the stored procedure completes. The behaviour you describe applies not to stored procedures (unless you use global temp tables instead of local temp tables).
Here is an example that creates a temp table within the stored procedure dbo.Test and tries to select from the table after the procedure completed:
CREATE PROCEDURE dbo.Test
AS
CREATE TABLE #Test (ColA int NOT NULL);
GO
EXEC dbo.Test;
GO
SELECT * FROM #Test;



Best Regards,
Chris Büttner
Post #560165
Posted Thursday, August 28, 2008 1:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 4, 2013 7:02 AM
Points: 465, Visits: 94
It should be 1,5,2,3,3,2
Post #560179
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse