[SQL] How to resolve 'syntax error near WHEN' in MERGE procedure?

I'm trying to make a merge statement that's part of a stored procedure which takes the following values as input:

@CreatedBy BIGINT,
@UpdatedBy BIGINT,
@UserIds NVARCHAR(MAX),
@TeamId BIGINT,
@TeamName NVARCHAR(255)


The procedure calls a function to separate the values in UserIds and store them into a temporary table along with the given @TeamId. Here is the code:

CREATE TABLE #tmpTeamUsers( TeamId BIGINT, UserId BIGINT )

INSERT INTO #tmpTeamUsers( TeamId, UserId ) SELECT @TeamId, Value AS UserId FROM [dbo].fn_splitStr(@UserIds, ',')

MERGE TeamUsers AS T
USING #tmpTeamUsers AS SRC
ON (T.TeamId = SRC.TeamId AND T.UserId = SRC.UserId)
WHEN MATCHED THEN
UPDATE SET UpdatedBy = @CreatedBy, UpdatedOn = @CreatedOn;
WHEN NOT MATCHED BY TARGET THEN
INSERT INTO TeamUsers(CreatedBy, CreatedOn, UpdatedBy, UpdatedOn, UserId, TeamId)
VALUES (@CreatedBy, @CreatedOn, @CreatedBy, @CreatedOn, SRC.UserId, @TeamId);
WHEN NOT MATCHED BY SOURCE THEN
DELETE;


I'm using MSSQL Management Studio 2014 v. 12.0.2000.8

An error message appears indicating a bad syntax near the first and second 'WHEN' and on 'SRC.UserId'. I tried changing the names of the aliases, using TARGET and SOURCE, expanding the temporary table definition to not have to use the input variables, but nothing works.

I thought maybe it could be an IntelliSense bug so I tried running it to no avail.

Does anyone have any tips?

Continue reading...