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.

JNK · Accepted Answer · 2013-06-27 17:16:35Z

4

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

answered Jun 27, 2013 at 17:16

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

You should explain it the other way round: Terminate the previous statement with a ; instead of "prepending" a ; to the CTE. sqlblog.org/2009/09/03/…

Rick Zoerner Over a year ago

That did it! Thanks for the lesson. I'll have to start getting better with terminating my statements.

JNK Over a year ago

@a_horse_with_no_name Don't tell Aaron, I'll never hear the end of it :P

Collectives™ on Stack Overflow

SQL Server syntax error for stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related