Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon

sql sql-server sql-server-2005 tsql common-table-expression
Duncan picture Duncan · Sep 17, 2009 · Viewed 69.7k times · Source

Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure

WITH SomeClause1 AS
(
  SELECT ....
)
WITH SomeClause2 AS
(
  SELECT ....
)

But the error occurs

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

What are my options? Is there any splitter I don't know about?

Answer

gbn picture gbn · Sep 17, 2009

Use a comma to separate CTEs

;WITH SomeClause1 AS
(
  SELECT ....
)
, SomeClause2 AS
(
  SELECT ....
)

Related questions

How can I use if statement after a CTE (SQL Server 2005)

Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE …

Read More
How we can use CTE in subquery in sql server?

How we can use CTE in subquery in sql server? like .. select id (i want to use CTE here), name from table_name

Read More
SQL 2005 CTE vs TEMP table Performance when used in joins of other tables

I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. …

Read More