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.