SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counts Puzzle


Counts Puzzle

Author
Message
YeshuaAgapao
YeshuaAgapao
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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



skyline666
skyline666
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 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
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

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



Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5315 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
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 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
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 125
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
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5315 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
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

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