SQL Server Error: A MERGE statement must be terminated by a semi-colon (;)

Viewed 12969

I have the following on a SQL Server 2014 database:

CREATE TABLE dbo.AssetGroupClassification  ( 
    ClassificationId    int IDENTITY(1,1) NOT NULL,
    ClassificationName  varchar(25) NOT NULL,
    CONSTRAINT [PK_AssetGroupClassification.ClassificationId] PRIMARY KEY CLUSTERED(ClassificationId)
)
GO
CREATE TYPE dbo.AssetGroupClassUDT AS TABLE (
    ClassificationId int IDENTITY (1, 1) NOT NULL, 
    ClassificationName varchar(25) NOT NULL
    )
GO

I'm attempting to create the below Stored Proc:

CREATE PROCEDURE dbo.AssetGroupClassUpsert
        @assetGrpClassData AssetGroupClassUDT READONLY
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON

    BEGIN TRY
        BEGIN TRANSACTION agcUpsertTran

        MERGE AssetGroupClassification AS t
        USING @assetGrpClassData AS src
        ON t.ClassificationId = src.ClassificationId
        WHEN MATCHED THEN
            UPDATE SET ClassificationName = src.ClassificationName
        WHEN NOT MATCHED THEN
            INSERT (ClassificationName)
            VALUES (src.ClassificationName);

        COMMIT TRANSACTION agcUpsertTran
    END TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        DECLARE @errMsg nvarchar(4000) = ERROR_MESSAGE()
        RAISERROR (@errMsg, 16, 1)

        RETURN 99   
    END CATCH
END
GO

However, I receive the error:

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

What am I missing? The semi-colon is clearly there so there must be something else up.

5 Answers

Just as a side node. If you're using Visual Studio Code with the MSSQL Plugin SQL Server (mssql), you will have the same problem. Even though the syntax is correct, on execution it complains about the missing semicolon.

When you are executing your script under the visual studio editor in script file, at end of the script you need to add ; after adding the semicolon VS will build the script successfully

Related