CTE Syntax

  • Eirikur Eiriksson (3/30/2015)


    ;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.

    😎

    Not terminating T-SQL statements with a semicolon is a deprecated feature (I think from SQL Server 2012); shouldn't everyone be encouraged to terminate their statements correctly? πŸ™‚

  • SQL-DBA-01 (3/29/2015)


    Thanks Steve for the nice question..as usual.

    I explored more on the cause.

    β€’You must remember to terminate the statement preceding the CTE with a semicolon, otherwise SQL will throw this error message at you:

    Incorrect syntax near the keyword β€˜with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    + 1

  • What's interesting to me is the usage of 'most'. I suspected c was right (since a and d were both wrong), but went looking to prove it. And out of 9 articles on MSDN and stackoverflow, only one started a CTE statement with a semicolon.

    Maybe it's just the way Dr. Google parsed my search, but that confused me because it was definitely not 'most'.

  • Add me to the list of people who understand the difference between a terminator and a begininator. A statement terminator belongs at the end of a statement and nowhere else. People are capable of learning, so teach them to do it the right way from the start.

  • GilaMonster (3/30/2015)


    Koen Verbeeck (3/30/2015)


    Such a bad habit to start a CTE with

    ;WITH

    I hate it πŸ˜€

    .This .It's a statement terminator .It has no place at the beginning of statements .I can't imagine anyone who writes front-end languages (C#, Java, C++, etc) ever even considering starting a statement with a terminator ?So why are we being so difficult about it and writing code that suggests to someone reading later that we don't even know the basic principals of programing languages

    I agree that treating it as a statement starter is just plain crazy. And results in ugly-looking code.

    But I also believe that if languages need to indicate statement boundaries it is better do it with a separator, not a terminator - it's only a small difference (in the case of C# it would simply remove the need for ";" before "}" so that there would no longer have to be some block closing brackets with a semicolon becfore them and some without) but it's something I've preferred for decades (I never understood why the C-languages broke from the separator-not-terminator tradition that all earlier languages based on Algol had followed, and became teh first Algol-family language with a terminator). Maybe it's just because I preferred the languages (apart from assembly languages) that I used most often my first years of writing software.

    Tom

  • GilaMonster (3/30/2015)


    .But starting statements with terminators teaches bad habits .For example I had a dev recently who scattered ; randomly until the SQL proc started working .Once it had been explained that the ; is a terminator just like in C# he put them correctly and asked me "Then why do people put them at the beginning of statements? That's just stupid." (.His words) .He'd read up on CTEs online and concluded ,based on some people putting the ; at the end and others at the beginning ,that it wasn't a statement terminator ,that it was just some command that no one explained what it did and that followed rules for placing that no one explained

    ?How about we just teach people correctly instead

    Eirikur Eiriksson (3/30/2015)


    ;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.

    😎

    .Without ; C# code won't work .We don't see samples of C# code starting with statement terminators

    +1.

    ;WITH makes sense to anyone who has been around a while, but without an explanation those newer to t-SQL (i.e. "accidental dba's") pick it up as "standard practice". By the time they figure it out, much code has been written and it isn't likely to get changed. I'd much prefer to see example code akin to:

    -- a CTE must either be the first statement in a batch, or the previous statement must be terminated with a semi-colon or an END

    [previous statement];

    WITH ...

    Ultimately, the responsibility for teaching good practices falls on those who know good practices.

  • I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.

  • Iwas Bornready (3/30/2015)


    I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.

    Then it's a perfect time to start fixing those bad habits.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Iwas Bornready (3/30/2015)


    I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.

    Shame on your teacher πŸ˜‰

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • GilaMonster (3/30/2015)


    Koen Verbeeck (3/30/2015)


    Such a bad habit to start a CTE with

    ;WITH

    I hate it πŸ˜€

    .This .It's a statement terminator .It has no place at the beginning of statements .I can't imagine anyone who writes front-end languages (C#, Java, C++, etc) ever even considering starting a statement with a terminator ?So why are we being so difficult about it and writing code that suggests to someone reading later that we don't even know the basic principals of programing languages

    .It hurts my eyes to see examples using a semi-colon to begin a CTE

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sipas (3/30/2015)


    Koen Verbeeck (3/30/2015)


    Such a bad habit to start a CTE with

    ;WITH

    I hate it πŸ˜€

    Nice question though, thanks Steve.

    +1 - maybe part of the answer should be promoting the correct way to write code, rather than the nasty workaround. Good question though.

    Give several answer options such as:

    ;WITH somecte ...

    WITH somecte ...

    BEGIN

    WITH Seomcte ...

    END

    ...

    GO

    WITH somecte...

    Then ask them to pick the 1 that is WRONG.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Eirikur Eiriksson (3/30/2015)


    ;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.

    😎

    A CTE will run without a semi-colon too. It just fails when/if there are multiple code segments in the same batch that have not been properly terminated.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Iwas Bornready (3/30/2015)


    I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.

    Not terminating statements with a semicolon is not only poor coding practice, it's also deprecated. In some future version, you'll be required to terminate your statements with a semicolon if you want them to work. In my opinion, this is as it should be.

  • Koen Verbeeck (3/30/2015)


    Such a bad habit to start a CTE with

    ;WITH

    I hate it :-D/quote]

    I totally agree! This leads to sloppy code development and debugging nightmares. Besides, it's ANSI standard to use semi-colon terminators and although SQL Server doesn't impose that at this time, it's a good habit to get into especially when you have to work in other RDBMS environments as well that do require them.

  • Eirikur Eiriksson (3/30/2015)


    ;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.

    😎

    I beg to differ.

    IF 'You'='Say so'

    ;WITH CTE AS(SELECT 1 n)

    SELECT *

    FROM CTE

    GO

    CREATE VIEW WillItBreak

    AS

    ;WITH CTE AS(SELECT 1 n)

    SELECT *

    FROM CTE

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply