Ken Carter Ken Carter - 6 months ago 12
SQL Question

Stored Procedure With CTE Fails to Create

I tested this query which was build with the help from another thread and it worked just fine moved it into a stored procedure where I need to feed it a parameter (value of EID). So the only changes I've made to the query which tested perfectly is to add the @param1 parameter at the top and bottom and the create procedure line at the top.

I can't save it because it is choking on the 4 line an the 'n' stating - SQL80001: 'n' is not a recognized option.

Been a long road to get to this point. Could someone help push me over the finish line here please?

CREATE PROCEDURE [dbo].[UpdateSubIdx]
@param1 varchar(30)

WITH n AS
(
SELECT DID AS DID,
sub_idx AS current_id,
ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
FROM GETT_Documents
)
--SELECT * FROM N
UPDATE GETT_Documents
SET sub_idx = n.new_id
FROM GETT_Documents G
JOIN n ON N.DID = G.DID
WHERE EID = 'AC-1.1.i';

---Repeat for the alt sub idx column renumberation
WITH n AS
(
SELECT DID AS DID,
alt_sub_idx AS current_id,
ROW_NUMBER() OVER (PARTITION BY EID ORDER BY sub_idx)*10 AS new_id
FROM GETT_Documents
)

UPDATE GETT_Documents
SET alt_sub_idx = n.new_id
FROM GETT_Documents G
JOIN n ON N.DID = G.DID
WHERE EID = @param1;


Regards,
Ken...

Answer

You need to put a semi-colon (;) immediately before the WITH keyword to terminate the previous statement.

Common Table Expressions must be preceded with a ; - more information on this can be read here: Why should a CTE start with a semi-colon?

You also need to use AS BEGIN ... END to mark the body of the procedure. Below are the changes that should work:

CREATE PROCEDURE [dbo].[UpdateSubIdx] @param1 varchar(30) 
As Begin

    ;WITH n AS 
    (
    SELECT  DID AS DID,
            sub_idx AS current_id,
            ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
    FROM    GETT_Documents 
    )
    --SELECT * FROM N
    UPDATE  GETT_Documents
            SET sub_idx = n.new_id
    FROM    GETT_Documents  G
    JOIN    n ON N.DID = G.DID
    WHERE   EID = 'AC-1.1.i';

    ---Repeat for the alt sub idx column renumeration
    ;WITH n AS 
    (
    SELECT  DID AS DID,
            alt_sub_idx AS current_id,  
    ROW_NUMBER() OVER (PARTITION BY EID ORDER BY sub_idx)*10 AS new_id
      FROM    GETT_Documents 
     )

    UPDATE  GETT_Documents
            SET alt_sub_idx = n.new_id
    FROM    GETT_Documents  G
    JOIN    n ON N.DID = G.DID
    WHERE   EID = @param1;
End