December 21, 2012 at 4:59 am
I was aware that CTE uses a ';' at the beginning. However I was just testing something and found that it runs perfectly without ';' at the beginning as well. How's that ?
December 21, 2012 at 5:23 am
the CTE declaration must be preceeded with a ';' if there is another Statement above it.
if there isn't a Statement above it, it isn't required for your cte to run.
probably still best that you put it in anyway, though.
December 21, 2012 at 6:16 am
sqlnaive (12/21/2012)
I was aware that CTE uses a ';' at the beginning.
Nope. Not at all. The ; is a statement terminator. It does not start statements any more than we start sentences in English with fullstops.
A CTE requires that the previous statement (if there is one) be terminated with a ;
SELECT name from sys.tables;
WITH Cols AS (SELECT name, object_name(object_id) AS TableName from sys.columns)
SELECT name from Cols ORDER BY TableName;
Get into the habit of terminating statements with the ;.
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
December 21, 2012 at 6:16 am
The CTE syntax itself does not require a leading semi-colon. Rather the preceding statement must be terminated with a semi-colon.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply