sql - 在 if 语句中添加 delete 语句

Question

我正在尝试将删除语句添加到现有的 IF 语句中。

我试过在开头和结尾添加语句，但它不起作用。

这是我为更新和插入语句创建的现有代码。

IF EXISTS (SELECT * FROM [int_SocialAccess].[dbo].[Authorisers] where ID = {ID})
update [int_SocialAccess].[dbo].[Authorisers] set 
        Name = {Name},
        Email = {Email},
        Responsibility = {Responsibility},
        IsHeadOfService = {IsHeadOfService},
        DeleteRec = {DeleteRec}
    where ID = {ID}
ELSE
    insert into [int_SocialAccess].[dbo].[Authorisers] 
    values( {Name},
        {Email},
        {Responsibility},
        {IsHeadOfService},
        'no')

我正在尝试在更新语句之前添加我的删除语句，如果“DeleteRec”字段是，我想删除记录，否则更新它。

IF {DeleteRec} = 'yes'
    DELETE FROM [int_SocialAccess].[dbo].[Authorisers]
    where ID = {ID}
ELSE

任何迹象都很好。

Answer 1

如果在一个 if-else block 中需要多个 stmt，则需要用 BEGIN END 换行 -

IF EXISTS (SELECT * FROM [int_SocialAccess].[dbo].[Authorisers] where ID = {ID})
BEGIN
    IF {DeleteRec} = 'yes'
        DELETE FROM [int_SocialAccess].[dbo].[Authorisers] where ID = {ID}
    ELSE
         Update
END
ELSE
    insert into [int_SocialAccess].[dbo].[Authorisers] 
    values( {Name},
        {Email},
        {Responsibility},
        {IsHeadOfService},
        'no')