SQL标量函数随机抛出“子查询返回多个值”错误求助

直接调用自定义标量函数dbo.uf_construction_price(16)时出现随机异常：无数据库变更的前提下，间隔几秒的两次调用，有时返回正确值，有时抛出错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

原函数定义 #

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[uf_construction_price]
(
    @id int
)
RETURNS numeric(19,6)
AS
BEGIN
    
    DECLARE @rounding INT
    SELECT @rounding = rounding from construction_settings WHERE construction_id = @id

    DECLARE @price NUMERIC(19,6)

    SET @price = (SELECT ISNULL(ROUND(SUM(ROUND(ROUND(ISNULL(bi.joc,0),2) * ROUND(ISNULL(bi.value,0),isnull(@rounding,3)),2)),2),0) 
    FROM budget_items bi 
    WHERE is_available != 1 AND construction_id = @id)

    RETURN isnull(@price,0.00)
END

调试发现的现象 #

• 直接执行函数内的查询语句时不会报错；
• 报错发生在SET @price =行，该查询偶尔会返回多个结果，但按SUM聚合逻辑本应只返回单值（加TOP 1后返回的是未求和的单行结果）；
• 兼容性设置疑似和问题相关，但未找到确凿证据；
• 将赋值逻辑改为临时表中转后，函数可稳定运行，修改后的代码如下：

修改后可正常运行的代码 #

declare @Temp TABLE (price numeric(19,6))
insert into @Temp
SELECT ISNULL(ROUND(SUM(ROUND(ROUND(ISNULL(bi.joc,0),2) * ROUND(ISNULL(bi.value,0),isnull(@rounding,3)),2)),2),0) 
FROM budget_items bi 
WHERE is_available != 1 AND construction_id = @id

SET @price = (SELECT * FROM @Temp)

问题原因与优化建议 #

核心原因 #

这个问题本质是标量函数上下文的查询计划异常：直接执行查询时SQL Server能正确生成聚合计划，但在标量函数内部，可能因兼容性级别、变量@rounding的延迟解析或查询计划缓存的异常复用，导致聚合逻辑失效，查询错误返回多行结果。

用临时表中转的方式，强制SQL Server先完成聚合查询并将结果存入临时表，再读取单值，相当于给查询添加了执行屏障，规避了计划异常的问题。

额外优化点 #

原函数中SELECT @rounding = rounding from construction_settings WHERE construction_id = @id存在隐患：如果construction_settings表中对应@id的记录有多行，@rounding会被随机赋值为其中一行的值，导致后续计算结果不稳定。建议添加TOP 1或确保construction_id是表的唯一键，比如：

SELECT TOP 1 @rounding = rounding from construction_settings WHERE construction_id = @id

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