1

Possible Duplicate:
Incorrect syntax near the keyword 'with'…previous statement must be terminated with a semicolon

I want to select hierachical data and insert it in a table. Therefore i need to use the WITH statment in my insert.

This works fine:

create table test_table
(
 id int
)   

with t_table 
as
(select 12345 wert)
insert into test_table (id)
select wert from t_table

But this generates a "wrong syntax near WITH keyword" error:

CREATE PROCEDURE p_insert_test
AS
BEGIN

with t_table 
as
(select 12345 wert)
insert into test_table (id)
select wert from t_table

END

I guess T-SQL doesn't like the WITH keyword before the INSERT keyword. How can i do this kind of insert in a stored procedure?

Thank you!

Community
  • 1
  • 1
jzzh
  • 23
  • 7
  • Can you edit your post with the exact code & error? Your first block of code has an error -- it either needs to be run as two statements, or have a GO put between the batches. Further, I was able to execute the procedure without error. While @JNK is correct that WITH has to be the first item in a batch, the only requirement I see here is to split the create table & the ad-hoc with-select. – EBarr Dec 12 '11 at 16:11
  • @EBarr - the first statement works, but the second does not. It sounds like you think the first statement didn't work. – JNK Dec 12 '11 at 16:15
  • That's correct. If you cut and paste the first block there is a syntax error (Msg 102, Level 15, State 1, Line 7. Incorrect syntax near 't_table'). It should have a "GO" between the create table and the WITH or a semicolon after the create table. – EBarr Dec 12 '11 at 23:27

1 Answers1

5

The CTE declaration needs to be the first command in the batch.

Just put a semicolon before the WITH and you should be fine:

;WITH t_Table...

EDIT:

Just to clarify why this is the case, the WITH keyword is used for query hints as well, such as WITH RECOMPILE, WITH (NOLOCK), etc. The query engine needs to know the intent of the WITH keyword, and the only way to signify that it represents a CTE is to ensure that the CTE declarations are the beginning of the batch.

Otherwise, you could have something ambiguous like:

SELECT Field
FROM MyTable
WITH CteName
As
(Select 1)

Without the statement terminator, CteName will be inappropriately read as a query hint applied to MyTable.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • 5
    +1 : Or, alternatively put, make sure the preceding command is terminated with a `;`. It's optional in most cases, but when using CTEs is a requirement to terminate the previous command 'correctly'. – MatBailie Dec 12 '11 at 16:06