我正在直接调用我的数据库中的用户定义函数.问题是,该函数似乎随机返回正确的值或抛出错误Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
,即使在数据库中没有更改,而且它与上一次调用相隔几秒钟.
函数调用语法:
select dbo.uf_construction_price(16)
个
功能主体:
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
我已经try 了很多事情来调试这个问题,这些是基于我的研究的更具描述性的行为:
- 当按原样调用Body时,它不会抛出错误.
- 错误抛出在包含
SET @price =
的行上,其中后续查询sometimes returns multiple results即使在SUM
不可能的情况下也会抛出错误(使用TOP 1时,它似乎返回未加和的结果) - 兼容性结果似乎与问题(不同的行为)相关,但同样,没有确凿的证据证明这一点.
- 当我用下面的代码片段替换第
SET @price =
行时,它似乎工作正常,但我也不知道为什么:
declare @Temp TABLE (id int)
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)