SQL Server UPDATE查询调用自定义函数报子查询返回多行错误问题

问题根本原因 #

• 该报错是SQL Server对标量用户自定义函数（UDF）的默认查询优化逻辑导致的：当你在批量UPDATE语句中直接调用标量UDF时，查询优化器会尝试将UDF内部的查询逻辑展开到外层UPDATE的执行计划中，而非逐行调用UDF。
• 你单独传入单个ID测试UDF时，函数内部子查询确实只会返回单个值，但优化器将逻辑展开后，外层UPDATE的多行上下文会和UDF内的子查询产生关联错误，优化器无法保证展开后的子查询对每一行外层更新行都只返回1个值，因此抛出子查询返回多值的错误。
• 游标逐行更新时相当于强制每次仅传入单个ID调用UDF，避免了优化器的展开操作，因此可以正常执行，但游标性能远低于集合操作，不推荐生产环境使用。

无游标的解决方案 #

方案1：将UDF逻辑改写为直接的UPDATE JOIN语句（性能最优，推荐优先使用） #

把两个UDF的逻辑直接整合到UPDATE的集合操作中，完全避免UDF调用：

UPDATE u
SET 
    CurrentLevel = cl.CurrentLevel,
    Rate = ISNULL(r.TotalRate / 86400.0, 0),
    LastUpdate = GETUTCDATE()
FROM Users u
-- 对应GetRate函数的计算逻辑
OUTER APPLY (
    SELECT 
        SUM(s.Level * (i.Bonus + ISNULL(b.BonusSize, 0))) as TotalRate
    FROM [Stats] s 
    JOIN [Orders] v ON s.Level > 0 AND s.id = v.id AND v.userID = u.id
    JOIN (SELECT id, SUM(ISNULL(Bonus,0)) as Bonus FROM Items GROUP BY id) i ON i.id = v.id
) r
-- 对应GetRate内的BonusSize查询逻辑
OUTER APPLY (
    SELECT TOP 1 BonusSize FROM Bonuses WHERE userID = u.id
) b
-- 对应GetCurrentLevel函数的逻辑，替换为该函数内部的实际查询即可
OUTER APPLY (
    SELECT 你函数内的返回字段 AS CurrentLevel FROM 对应表 WHERE 关联条件 = u.id
) cl
WHERE u.id IN (1,2,3)

方案2：强制UDF逐行调用（适合不想改写现有UDF逻辑的场景） #

如果你不想修改现有UDF的代码，可以通过APPLY运算符强制逐行调用UDF，规避优化器的展开逻辑：

UPDATE u
SET 
    CurrentLevel = cl.val,
    Rate = r.val,
    LastUpdate = GETUTCDATE()
FROM Users u
CROSS APPLY (SELECT [dbo].[GetCurrentLevel](u.id) as val) cl
CROSS APPLY (SELECT [dbo].[GetRate](u.id) as val) r
WHERE u.id IN (1,2,3)

如果UDF可能返回空值，将CROSS APPLY替换为OUTER APPLY即可。

方案3：将标量UDF转换为内联表值函数（兼顾性能和代码复用性） #

内联表值函数的优化效率远高于标量UDF，且不会出现展开导致的多值错误，以GetRate为例改写后的函数代码如下：

CREATE FUNCTION [dbo].[GetRate_Inline]
(
    @userID int
)
RETURNS TABLE
AS
RETURN (
    SELECT ISNULL(SUM(s.Level * (i.Bonus + ISNULL(b.BonusSize, 0)))/86400.0, 0) as Rate
    FROM (SELECT TOP 1 BonusSize FROM Bonuses WHERE userID = @userID) b
    JOIN [Orders] v ON v.userID = @userID
    JOIN [Stats] s ON s.Level > 0 AND s.id = v.id
    JOIN (SELECT id, SUM(ISNULL(Bonus,0)) as Bonus FROM Items GROUP BY id) i ON i.id = v.id
)

调用方式：

UPDATE u
SET 
    CurrentLevel = cl.CurrentLevel,
    Rate = r.Rate,
    LastUpdate = GETUTCDATE()
FROM Users u
CROSS APPLY [dbo].[GetRate_Inline](u.id) r
-- 同理将GetCurrentLevel改写为内联表值函数后调用
CROSS APPLY [dbo].[GetCurrentLevel_Inline](u.id) cl
WHERE u.id IN (1,2,3)

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