SQL函数只返回“1”(SQL Function just returns “1”)

SQL函数只返回“1”(SQL Function just returns “1”)

当我尝试用select选择结果时，无论输入数是什么，这个函数只返回“1”。

Create Function dbo.LotValue (@IdAssetGroup as bit, @TdValueBase as real, @TdPrice as real, @IdRiskfactor as bit) RETURNS real as begin DECLARE @Output AS real set @Output = (case when @IdAssetGroup in (1, 8) then 1 when @IdAssetGroup in (2,3,4,6,7) then (@TdValueBase / @TdPrice) when @IdAssetGroup in (5) then Case @IdRiskfactor when 8 then 1 --BLFT else 0.01 end when @IdAssetGroup in (9) then case when @IdRiskfactor in (1,42,84,113) then @TdValueBase --AUD, EUR,NZD,GBP when @IdRiskfactor in (25,62) then (@TdValueBase / @TdPrice) --NDF: BRL, KRW else (@TdValueBase / @TdPrice) *-1 --others FX end end) return @Output; end

它只返回“1”：

select dbo.lotvalue(5,20,5,42)

This function is just returning "1"as result no matter input number when I try to get result with select.

Create Function dbo.LotValue (@IdAssetGroup as bit, @TdValueBase as real, @TdPrice as real, @IdRiskfactor as bit) RETURNS real as begin DECLARE @Output AS real set @Output = (case when @IdAssetGroup in (1, 8) then 1 when @IdAssetGroup in (2,3,4,6,7) then (@TdValueBase / @TdPrice) when @IdAssetGroup in (5) then Case @IdRiskfactor when 8 then 1 --BLFT else 0.01 end when @IdAssetGroup in (9) then case when @IdRiskfactor in (1,42,84,113) then @TdValueBase --AUD, EUR,NZD,GBP when @IdRiskfactor in (25,62) then (@TdValueBase / @TdPrice) --NDF: BRL, KRW else (@TdValueBase / @TdPrice) *-1 --others FX end end) return @Output; end

it just return "1":

select dbo.lotvalue(5,20,5,42)

最满意答案

你每次都会遇到你的第一个案例（ when @IdAssetGroup in (1, 8) then 1 @IdAssetGroup when @IdAssetGroup in (1, 8) then 1 ）因为@IdAssetGroup是一个bit ，它只能是1或0。

你也在使用@IdRiskFactor做同样的@IdRiskFactor ，我想这需要改变。 我假设你想要这些是INT 。

CREATE FUNCTION dbo.LotValue ( @IdAssetGroup as INT, @TdValueBase as real, @TdPrice as real, @IdRiskfactor as INT ) RETURNS real

You are hitting your first case every time (when @IdAssetGroup in (1, 8) then 1) because @IdAssetGroup is a bit, which can only ever be 1 or 0.

You're doing the same with @IdRiskFactor as well, which I imagine needs changed. I assume you want these to be INTs.

CREATE FUNCTION dbo.LotValue ( @IdAssetGroup as INT, @TdValueBase as real, @TdPrice as real, @IdRiskfactor as INT ) RETURNS real