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


Temp Tables


Temp Tables

Author
Message
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5822 Visits: 11394
Comments posted to this topic are about the item Temp Tables
yogyogi97
yogyogi97
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 152
Thanks for the question. easy one on last day of week
+1
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 569
Good question. Just yesterday Hugo is explaining about parsing in another QotD.

--
Dineshbabu
Desire to learn new things..
Danny Ocean
Danny Ocean
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: 1348 Visits: 1549
I really like this question. It's a very import basic question.
Thanks for question. :-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
Easy one to end the workweek. Thanks!



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10690 Visits: 11991
Quite a fun question, and a good explanation. Thanks for that.

It rather nicely shows up the pitfalls of doing a half-way decent job of parsing. The "table already exists" message is of course spurious - the table doesn't exist - but could easily mislead someone into wasting time looking for the "existing" table. If the contents of the THEN and ELSE branches were swapped, the use of USER as a column name would be detected as a syntax error and the table already exists message would not be generated. If the THEN branch is as in the question and the ELSE branch and the keyword ELSE are deleted, there will be no syntax error but DROP TABLE will fail at run time with an object doesn't exist error. This behaviour is pretty silly.

This muddle doesn't exists when creating a view, a procedure, or a function, because rules were introduced to prevent it. Perhaps the MS team considered that permitting conditional table creation was more important than avoiding the muddle. In my view it would have been better to have a rule for table creation of same protective type as the rule for views and avoid the muddle. Or of course to have a parser that detects multiple errors, like the parsers for many other languages, instead of giving up on the first one, and/or to keep the rule that statements that create a table with can't appear twice in the same batch regardless of control flow but provide an appropriate error message quite distinct from the "table already exists" error message.

Good practise is of course never to write code that includes conditional table creation, and it's common for it to be the first statement in a batch (the preceding batch is a conditional drop). So requiring a table creation statement to come before any non-DDL code in a batch would work; but procs have to have a batch each, and a view definition has to be the first statement in a batch, it can't be preceded by any statement, not just not by any non-DDL statement, so I wouldn't see any difficulty with requiring create table to the first statement in a batch. But there's a risk that it would kill a lot of the code out there in the wild, because best practise is not followed by all. So probably it's now too late to do "the right thing" and protect create table in the same way as create view. That makes the parser that doesn't give up the best option, and I suspect that that would be a lot of work starting from the current parser - too much work for MS to consider doing it.

Tom

Mike Is Here
Mike Is Here
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 513
Thanks for the question. I only remember this from a different compiler question.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
Thanks for the question... easy one.... only because I've tried to do this too many times in the past.

I no longer try to do this... because I've found it generally better to make a pair of stored procs, one for each branch and either call them from another "switchboard" proc or have the code itself determine which one to call.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1713 Visits: 1949
Danny Ocean (5/24/2013)
I really like this question. It's a very import basic question.
Thanks for question. :-)


+1
Thanks!
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2009 Visits: 1189
Easy one for the day

Malleswarareddy
I.T.Analyst
MCITP(70-451)
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