SQL Server syntax error for stored procedure

Question

I am getting a syntax error trying to create a stored procedure, although the exact same code runs perfectly as a query. I can't see the error myself, so any help would be appreciated.

The error is:

Msg 102, Level 15, State 1, Procedure DataSelect, Line 12 Incorrect syntax near 'OrderedYTD'.

And the code is simply:

CREATE PROCEDURE DataSelect
(
@TargetPdc int
)
AS
BEGIN

    -- Refresh Data Here
    EXEC DataUpdate

    -- Select Data for Report
    WITH OrderedYTD AS
    (
        SELECT custextract.*, histextract.*,
      ROW_NUMBER () OVER (PARTITION BY custextract.custcustno ORDER BY histextract.salesytd desc) AS RowNumber
        FROM custextract 
        INNER JOIN histextract 
            ON custextract.custcustno = histextract.histcustno
        WHERE (custextract.ecall = 'Y')
    ) 

SELECT OrderedYTD.*
FROM OrderedYTD
WHERE RowNumber <= 10 and pdc = @TargetPdc;

END

I've run everything beginning at the WITH statement (minus the variable in the WHERE clause) as a query multiple times with no issue. Is there a syntactic difference using the CTE inside a stored procedure? Thanks.

Answer 1

You need a semicolon before your WITH, otherwise it gets treated as a modifier to the preceding statement. Just change this line and it should work:

EXEC DataUpdate;

If you don't terminate all your statements with semicolons, standard practice is to put them before your CTE definition:

;WITH OrderdYTD AS