May 18, 2011 at 3:34 pm
Hi All,
Can I have CTE in stored procs? I understand that the end of CTE is identified with a semi colon. I used that and got an error stating to specify the end of CTE. So I used a GO statement and it ignores rest of the code in the proc.
Thanks
May 18, 2011 at 3:50 pm
A CTE is just a part of a normal DML statement. It can be included with any SELECT, INSERT, UPDATE or DELETE statement.
You are going to have to show us what you attempted so we can identify where the problem is. The basic structure of a CTE is:
WITH cte (col1, col2)
AS (SELECT col1, col2 FROM sometable WHERE somecol = somevalue) -- this is the end of the CTE
SELECT col1, col2
FROM cte;
Now, the semi-colon is a statement terminator in SQL. It just denotes the end of a statement - and with a common table expression, the previous statement must be terminated using the semi-colon or it will not compile.
For example:
SET @var = 'somevalue'
WITH cte (...) AS (SELECT ...)
The above will fail because the previous statement was not terminated with a semi-colon.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply