Incorrect syntax near the keyword 'with' while creating stored procedure?

Question

Below is the stored procedure which showing error new with keyword:

Below procedure written to update existing records, delete records if not exists in json input parameter and insert if no match found. But below issue not even let to create new stored procedure

CREATE PROCEDURE usp_PaymentRecommendationsInsertUpdate
    @PaymentRecommendationsJson NVARCHAR(MAX)
    AS
    BEGIN
        MERGE INTO PaymentRecommendations AS PR
        USING ( 
                    SELECT      ClaimId,
                                CoverageTypeId,
                                Payee,
                                IndemnityPmtRequestAmnt,
                                ExpensePaymentRequestAmnt,
                                RequestDate,
                                Memo,
                                CheckNumber,
                                IssueDate,
                                CreatedOn,
                                CreatedBy,
                                ModifiedOn,
                                ModifiedBy
                    FROM    OPENJSON(@PaymentRecommendationsJson)
                            WITH (
                                        ClaimId BIGINT,
                                        CoverageTypeId SMALLINT,
                                        Payee VARCHAR(256),
                                        IndemnityPmtRequestAmnt MONEY,
                                        ExpensePaymentRequestAmnt MONEY,
                                        RequestDate DateTime,
                                        Memo VARCHAR(512),
                                        CheckNumber VARCHAR(16),
                                        IssueDate DATETIME,
                                        CreatedOn DATETIME,
                                        CreatedBy BIGINT,
                                        ModifiedOn DATETIME,
                                        ModifiedBy BIGINT
                                )) AS InputJSON
                   ON (PR.ClaimId = InputJSON.ClaimId)
        WHEN MATCHED THEN
            UPDATE SET  ClaimId = InputJSON.ClaimId,
                        CoverageTypeId = InputJSON.CoverageTypeId,
                        Payee = InputJSON.Payee,
                        IndemnityPmtRequestAmnt = InputJSON.IndemnityPmtRequestAmnt,
                        ExpensePaymentRequestAmnt = InputJSON.ExpensePaymentRequestAmnt,
                        RequestDate = InputJSON.RequestDate,
                        Memo = InputJSON.Memo,
                        CheckNumber = InputJSON.CheckNumber,
                        IssueDate = InputJSON.IssueDate,
                        CreatedOn = InputJSON.CreatedOn,
                        CreatedBy = InputJSON.CreatedBy,
                        ModifiedOn = InputJSON.ModifiedOn,
                        ModifiedBy = InputJSON.ModifiedBy
        WHEN NOT MATCHED THEN
            INSERT (    ClaimId,
                        CoverageTypeId,
                        Payee,
                        IndemnityPmtRequestAmnt,
                        ExpensePaymentRequestAmnt,
                        RequestDate,
                        Memo,
                        CheckNumber,
                        IssueDate,
                        CreatedOn,
                        CreatedBy,
                        ModifiedOn,
                        ModifiedBy)
            VALUES (    InputJSON.ClaimId,
                        InputJSON.CoverageTypeId,
                        InputJSON.Payee,
                        InputJSON.IndemnityPmtRequestAmnt,
                        InputJSON.ExpensePaymentRequestAmnt,
                        InputJSON.RequestDate,
                        InputJSON.Memo,
                        InputJSON.CheckNumber,
                        InputJSON.IssueDate,
                        InputJSON.CreatedOn,
                        InputJSON.CreatedBy,
                        InputJSON.ModifiedOn,
                        InputJSON.ModifiedBy);
        WHEN NOT MATCHED BY SOURCE THEN
            DELETE
    END

Do we need to handle it as normal way in azure database?

Answer 1

Remove the semi-colon at the end of your proc, just after ... InputJSON.ModifiedBy)*;*, then add a semi-colon after the final DELETE, like this:

CREATE PROCEDURE usp_PaymentRecommendationsInsertUpdate
    @PaymentRecommendationsJson NVARCHAR(MAX)
    AS
    BEGIN
        MERGE INTO PaymentRecommendations AS PR
        USING ( 
                    SELECT      ClaimId,
                                CoverageTypeId,
                                Payee,
                                IndemnityPmtRequestAmnt,
                                ExpensePaymentRequestAmnt,
                                RequestDate,
                                Memo,
                                CheckNumber,
                                IssueDate,
                                CreatedOn,
                                CreatedBy,
                                ModifiedOn,
                                ModifiedBy
                    FROM    OPENJSON(@PaymentRecommendationsJson)
                            WITH (
                                        ClaimId BIGINT,
                                        CoverageTypeId SMALLINT,
                                        Payee VARCHAR(256),
                                        IndemnityPmtRequestAmnt MONEY,
                                        ExpensePaymentRequestAmnt MONEY,
                                        RequestDate DateTime,
                                        Memo VARCHAR(512),
                                        CheckNumber VARCHAR(16),
                                        IssueDate DATETIME,
                                        CreatedOn DATETIME,
                                        CreatedBy BIGINT,
                                        ModifiedOn DATETIME,
                                        ModifiedBy BIGINT
                                )) AS InputJSON
                   ON (PR.ClaimId = InputJSON.ClaimId)
        WHEN MATCHED THEN
            UPDATE SET  ClaimId = InputJSON.ClaimId,
                        CoverageTypeId = InputJSON.CoverageTypeId,
                        Payee = InputJSON.Payee,
                        IndemnityPmtRequestAmnt = InputJSON.IndemnityPmtRequestAmnt,
                        ExpensePaymentRequestAmnt = InputJSON.ExpensePaymentRequestAmnt,
                        RequestDate = InputJSON.RequestDate,
                        Memo = InputJSON.Memo,
                        CheckNumber = InputJSON.CheckNumber,
                        IssueDate = InputJSON.IssueDate,
                        CreatedOn = InputJSON.CreatedOn,
                        CreatedBy = InputJSON.CreatedBy,
                        ModifiedOn = InputJSON.ModifiedOn,
                        ModifiedBy = InputJSON.ModifiedBy
        WHEN NOT MATCHED THEN
            INSERT (    ClaimId,
                        CoverageTypeId,
                        Payee,
                        IndemnityPmtRequestAmnt,
                        ExpensePaymentRequestAmnt,
                        RequestDate,
                        Memo,
                        CheckNumber,
                        IssueDate,
                        CreatedOn,
                        CreatedBy,
                        ModifiedOn,
                        ModifiedBy)
            VALUES (    InputJSON.ClaimId,
                        InputJSON.CoverageTypeId,
                        InputJSON.Payee,
                        InputJSON.IndemnityPmtRequestAmnt,
                        InputJSON.ExpensePaymentRequestAmnt,
                        InputJSON.RequestDate,
                        InputJSON.Memo,
                        InputJSON.CheckNumber,
                        InputJSON.IssueDate,
                        InputJSON.CreatedOn,
                        InputJSON.CreatedBy,
                        InputJSON.ModifiedOn,
                        InputJSON.ModifiedBy)
        WHEN NOT MATCHED BY SOURCE THEN
            DELETE;
    END

A MERGE statement must be terminated by a semi-colon (;)

You may also be dealing with a compatibility issue. Set your database compatibility to 140, eg:

ALTER DATABASE yourDatabase SET COMPATIBILITY_LEVEL = 140;