CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012common-table-expression
Raider of the lost BBS
Raider of the lost BBS

Reputation: 258

Execute multiple statements and CTE in stored procedure in SQL Server 2012?

I'm newish to SQL server 2012, and I'm trying to create a stored procedure that should:

  1. Delete previous data from a table, based on a parameter, then
  2. Insert new data on that table.

But I need to use CTEs for performance and other reasons (please don't get hung up on that, I have to, just take my word).

The CTE query works fine if it's by itself in a Stored Procedure, but I can't get the SP to work with two clauses.

I get an error when trying to create the procedure, complaining that I should use a semicolon before the CTE. If I add the semicolon, SQL Server complains about it too. It's driving me insane, please help!

Table where I want to delete/insert:

CREATE MYTABLE ( APPUSER NVARCHAR(15), DATA NVARCHAR(100) )

Simplified stored procedure (no semicolon):

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER )

    WITH CTE AS (
        SELECT DATA
        FROM SOURCETABLE
        WHERE ( TYPE = @TYPE )
    )
    INSERT INTO MYTABLE
    SELECT
        @SOMEUSER,
        DATA
    FROM CTE 
END

Error message without semicolon:

[Error Code: 319, SQL State: S1000] Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Please note that even though that's a simplified version of the actual query, the error is exactly the same. I did try the above code, without any luck :(

Stored procedure with semicolon (same query as above, showing only the semicolon for brevity):

(...)
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER ); /* Semicolon */

    WITH CTE AS (    
(...)

Error with semicolon:

[Error Code: 102, SQL State: 42000] Incorrect syntax near ')'.

I tried enclosing the DELETE clause in it's own transaction, that didn't work either, always get one of the two errors.

Any pointers will be very appreciated, thanks!!!

Upvotes: 3

Views: 62606

Answers (3)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

For complete clarity of the points raised by others, this excerpt form the MSDN documentation on CTE's in SQL Server is an essential note for usage of CTE's:

•When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

As also noted, it is best practice to either:

  • Develop the habit of ending all SQL statements with a semicolon; or
  • Learn and memorize all required semicolons, such as here, and preface all these constructs with one.

I prefer the first, but I have seen competent developers who prefer the second.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

You have something strange going on because the following works for me: 

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM [Table_1] WHERE ( [lname]=@SOMEUSER );

    WITH CTE AS (
        SELECT [fname], [lname]
        FROM [Table_1]
        WHERE ( [ID] = @TYPE )
    )
    INSERT INTO [Table_1]
    SELECT top 1
        @SOMEUSER,
        [lname],
        @TYPE
    FROM CTE 
END

Check your environment

A CTE is just syntax - it does not help performance

Upvotes: 4

Rahul Bhati
Rahul Bhati

Reputation: 296

You need to put a ';' before 'With' keyword, your code look like this

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER )

    ;WITH CTE AS (
        SELECT DATA
        FROM SOURCETABLE
        WHERE ( TYPE = @TYPE )
    )
    INSERT INTO MYTABLE
    SELECT
        @SOMEUSER,
        DATA
    FROM CTE 
END

As a thumb rule for CTE, always start with a semicolon if you have any executable statement above your CTE.

Upvotes: 11

Related Questions