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

Question

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.

Answer 1

Strictly speaking all SQL statements should be terminated with a semicolon. i.e.

SELECT
    Example
FROM
    Test1
;

SELECT
    Example
FROM
    Test2
;

In practice SQL Server only enforces this for certain query types, for now. Statements that precede CTEs and MERGEs are an example of this.

From MSDN:

; Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

Of course it doesn't help that the error message tells you to terminate the merge but doesn't mention the preceding query is also required. Try:

BEGIN TRANSACTION agcUpsertTran;

    MERGE AssetGroupClassification AS t
    ....
    ;

Answer 2

I ran the same script in Server Explorer within Visual Studio 2013 and the stored proc was created without any problems. I assume the issue is to do with the IDE I'm using, Aqua Data Studio 15.0.11.