Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counts Puzzle


Counts Puzzle

Author
Message
YeshuaAgapao
YeshuaAgapao
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 211
You forgot to put drops for you temp tables (#data, #category) at the end of the code block.



skyline666
skyline666
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
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 Wink.
YeshuaAgapao
YeshuaAgapao
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 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.



Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6281 Visits: 1407
Good Question...



Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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
skyline666
skyline666
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
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 Wink.
Aleksandr Furman
Aleksandr Furman
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 114
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 Wink.

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.
skyline666
skyline666
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
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 Wink.

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!
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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 Wink.

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
Giri Duddu
Giri Duddu
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 94
It should be 1,5,2,3,3,2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search