如何修复带表值参数的SQL UPDATE子查询存储过程执行报错问题

问题原因 #

你现有的代码存在两个明显问题：

• 存储过程的参数列表开头多了多余的逗号，会直接触发语法错误
• UPDATE语句的FROM子句没有和目标更新表PersonXNotifyUser建立关联条件，数据库无法定位需要更新的匹配行，要么直接报错要么会误更新全表记录

修正后的完整代码 #

CREATE TYPE dbo.GuidIDList
AS TABLE
(
    ID [UNIQUEIDENTIFIER]
);

CREATE PROCEDURE [dbo].[PersonXNotifyUser_InsertUpdate]
(
    @UserID [UNIQUEIDENTIFIER],
    @NotifyUserIDs AS dbo.GuidIDList READONLY,
    @EditingUserID [UNIQUEIDENTIFIER]
)
AS
SET NOCOUNT ON

UPDATE pnu
SET 
    IsDeleted = 0, 
    ModifiedBy = @EditingUserID, 
    ModifyDate = GETUTCDATE()
FROM PersonXNotifyUser pnu
-- 直接关联传入的通知用户ID列表
INNER JOIN @NotifyUserIDs ids
    ON pnu.NotifyUserID = ids.ID
-- 匹配指定的UserID
WHERE pnu.UserID = @UserID

逻辑说明 #

不需要嵌套多层子查询实现需求，直接将目标表和表值参数做关联匹配即可，逻辑更清晰，执行性能也更优。如果更习惯用EXISTS写法，也可以替换为以下UPDATE逻辑：

UPDATE pnu
SET 
    IsDeleted = 0, 
    ModifiedBy = @EditingUserID, 
    ModifyDate = GETUTCDATE()
FROM PersonXNotifyUser pnu
WHERE 
    pnu.UserID = @UserID
    AND EXISTS (
        SELECT 1 FROM @NotifyUserIDs ids 
        WHERE ids.ID = pnu.NotifyUserID
    )

内容的提问来源于stack exchange，提问作者blgrnboy