Merge statement, where you insert or delete depending on parameter

Question

I want to make an efficient sproc that either inserts a row or deletes.
If a row exist, and the @action is false, i want to delete the row.
If the row exists but @action is set to true no actions should be performed
If the row doesnt exist i want to insert it

Its for a checkboxlist, so i was going to loop through each listItem and call the sproc, doesnt seem very efficient :(

I saw merge syntax for sql and tried to do this....

@slotID int,
@tagID int,
@action text


MERGE slotTagBridge as stb
USING (SELECT * FROM slotTagBridge where slotID = @slotID AND tagID = @tagID) AS stub
ON stb.id = stub.id
WHEN MATCHED AND @action = 'False' THEN DELETE
WHEN NOT MATCHED THEN
INSERT(slotID,tagID)
VALUES(@slotID,@tagID);

But i get two errors, and sql is not very helpful at identifying the problem. Any help would be fantastic!

Answer 1

I suggest checking your version. The following doesn't give me any syntax errors on SQL Server 2008.

create table slotTagBridge
(
id int,
slotID int,
tagID int
)

declare @slotID int,
@tagID int,
@action nvarchar(max)


MERGE slotTagBridge as stb
USING (SELECT * FROM slotTagBridge where slotID = @slotID AND tagID = @tagID) AS stub
ON stb.id = stub.id
WHEN MATCHED AND @action = 'False' THEN DELETE
WHEN NOT MATCHED THEN
INSERT(slotID,tagID)
VALUES(@slotID,@tagID);