title: Execute multiple statements and CTE in stored procedure in SQL Server 2012?
body: <p>I'm newish to SQL server 2012, and I'm trying to create a stored procedure that should: </p>
  
  <ol>
  <li>Delete previous data from a table, based on a parameter, then</li>
  <li>Insert new data on that table.</li>
  </ol>
  
  <p>But I need to use <strong>CTE</strong>s for performance and other reasons (<em>please</em> don't get hung up on that, I have to, just take my word). </p>
  
  <p>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.</p>
  
  <p>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, <strong>please help</strong>!</p>
  
  <p>Table where I want to delete/insert:</p>
  
  <pre><code>CREATE MYTABLE ( APPUSER NVARCHAR(15), DATA NVARCHAR(100) )
  </code></pre>
  
  <p>Simplified stored procedure (no semicolon):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Error message without semicolon:</p>
  
  <blockquote>
    <p>[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.</p>
  </blockquote>
  
  <p>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 :(</p>
  
  <p>Stored procedure with semicolon (same query as above, showing only the semicolon for brevity):</p>
  
  <pre><code>(...)
  BEGIN
      DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER ); /* Semicolon */
  
      WITH CTE AS (    
  (...)
  </code></pre>
  
  <p>Error with semicolon:</p>
  
  <blockquote>
    <p>[Error Code: 102, SQL State: 42000]  Incorrect syntax near ')'.</p>
  </blockquote>
  
  <p>I tried enclosing the DELETE clause in it's own transaction, that didn't work either, always get one of the two errors.</p>
  
  <p>Any pointers will be very appreciated, thanks!!!</p>