x

semicolon prior to CTE

Hello. A couple months ago I added a CTE snippet within an existing SP but failed to terminate the statement above it with a semicolon. NBD, it ran perfectly in my development environment. Then it ran perfectly in my production environment... until early January (took me a month or so to discover this since it was embedded within an SSIS package that allowed continuation upon errors). Now the semicolon is required in production BUT the original version WITHOUT the semicolon still works in development. The SQL versions are identical.

"Microsoft SQL Server 2005 - 9.00.4035.00 (Intel IA-64) Nov 24 2008 12:59:53 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) "

I took a quick look at possible configuration differences but didn't see any. Anyone know why this might be, why one SQL Server will spit out a syntax error yet another seemingly identical SQL machine allow to run?

Thanks, Ken

[EDIT] Code added from sample in answer

Select @IncorrectAssign = Count(*) From d_People p1 Inner Join d_People p2 On p2.SupervisorId = p1.EmployeeId  And p2.EffectiveDate = p1.EffectiveDate And p2.EffectiveDate = @FromDate  Where p2.SupPeopleId <> p1.PeopleId Select @IncorrectAssign = Coalesce(@IncorrectAssign, 0) ;With DupEmployees (EmployeeId) As (Select EmployeeId From d_People  Where Effectivedate = @FromDate And EmployeeId Is Not Null  Group By Employeeid Having Count(*) > 1) Select @DuplicateEntires = Count(*) From DupEmployees 
more ▼

asked Mar 03 '10 at 03:20 PM in Default

Ken Trock gravatar image

Ken Trock
53 1 1 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort oldest

The semicolon is needed for scenarios where WITH could be considered part of the previous query. Many people now put one ahead if any CTE, but this can even cause problems. It is always best practice to end SQL statements in semicolons, rather than prepending them to the following statement.

The situation with CTEs is interesting because a preceding statement that doesn't support WITH hints, such as the BEGIN statement, or a SET operation, could let someone who considers that CTEs start with a semicolon believe that there is some inconsistency in the CTE construct.

I think you'll find that a variable assignment without a FROM clause may not support WITH hints, and that this is the cause of confusion.

more ▼

answered Mar 03 '10 at 07:11 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

Congrats on the 3k mate :)
Mar 03 '10 at 07:34 PM Matt Whitfield ♦♦
Thx. Now let me find one of your questions to vote to close... ;)
Mar 04 '10 at 06:29 AM Rob Farley
Interesting. Thanks for the clarification.
Mar 05 '10 at 02:25 PM Ken Trock
(comments are locked)
10|1200 characters needed characters left

Is there any chance we could see the code (perhaps after being sanitized)?

Generally in SQL Server a semicolon is required on the last command before a CTE, unless the CTE is the first element in a batch. This means you do not need a semicolon on a CTE that is immediately after a GO statement since it is starting the new batch. Of course, that should not happen in a stored procedure, but you could get a similar effect through the use of dynamic sql. Every time dynamic sql is executed, it does so in its own batch.

As a habit, I always start the line that the cte is on with a semicolon, this makes it a nonissue.

more ▼

answered Mar 03 '10 at 03:55 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

I'm not a fan of ";WITH". I prefer to start CTEs with a comment that says --Make sure the previous statement ended with a semicolon.
Mar 03 '10 at 07:14 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left

Sure. I've included the key part of my SP; the snippet prior to my new CTE and the CTE itself. Basically, I later email @IncorrectAssign and @DuplicateEntires to a few people via sp_Send_DBMail.

Select @IncorrectAssign = Count(*) From d_People p1 Inner Join d_People p2 On p2.SupervisorId = p1.EmployeeId  And p2.EffectiveDate = p1.EffectiveDate And p2.EffectiveDate = @FromDate  Where p2.SupPeopleId <> p1.PeopleId Select @IncorrectAssign = Coalesce(@IncorrectAssign, 0) ;With DupEmployees (EmployeeId) As (Select EmployeeId From d_People  Where Effectivedate = @FromDate And EmployeeId Is Not Null  Group By Employeeid Having Count(*) > 1) Select @DuplicateEntires = Count(*) From DupEmployees 
more ▼

answered Mar 03 '10 at 04:20 PM

Ken Trock gravatar image

Ken Trock
53 1 1 2

Is the code on the dev server exactly the same as this snippet with the exception of the semi-colon?
Mar 03 '10 at 04:36 PM CirqueDeSQLeil

Thanks. It is a lot easier to read if you mark your code sample as code though, it helps with the formatting.

It looks like this should not work as is without the semicolon though.
Mar 03 '10 at 04:42 PM TimothyAWiseman
Timothy, the semicolon starts the CTE line rather than end the previous line ...
Mar 03 '10 at 04:57 PM Fatherjack ♦♦
Best if you can edit the question to put the code in there, as these answers will get reordered once you start upvoting them.
Mar 03 '10 at 07:12 PM Rob Farley
Fatherjack, you are right. I apparently have gone blind today. Thanks.
Mar 04 '10 at 01:04 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Every statement should end with a semicolon. Writing statements without terminating semicolons is deprecated and will not be supported in future according to Microsoft.

Having said that, there is at least some room for doubt about what "every statement" means. In previous versions of SQL Server certain statements were not allowed to be terminated with a semicolon. Now some require it but in most places the syntax checker doesn't mind either way.

Microsoft has created a legacy of problems by not having better rules and syntax checking. Even though not using semicolons is deprecated there is still no built in way to check for it so unfortunately people will continue to produce non-conforming code. Basically it's a mess and I suspect it will take several versions to sort this out properly. Meantime my advice is to put in semicolons wherever you can.

more ▼

answered Mar 03 '10 at 08:28 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

As a bit of a side bar, I deplore the fact that Microsoft is moving in the direction of requiring semi-colons for each statment. I guess it's better than requiring continuation characters at the end of each line, though.

more ▼

answered Mar 06 '10 at 10:56 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 1 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x36

asked: Mar 03 '10 at 03:20 PM

Seen: 3076 times

Last Updated: Mar 04 '10 at 05:50 AM