表值函数作为参数传递以在case语句内生成输出

debugcn 发表于 Dev

米塔利·纳斯（Mitali Nath）

我的数据集如下所示

SQL Server 2012。

DECLARE @ELECTRONICS TABLE
(
    RESISTORID INT, 
    CAPACITORID VARCHAR(10),    
    VOLT  DECIMAL(6,2), 
    WATT  INT,  
    PASSIVENUMBER INT    
) 

INSERT @ELECTRONICS
SELECT 100, 'TH',   1.2,    5,  93 UNION ALL
SELECT 200, 'TH',   1.2,    5,  93 UNION ALL
SELECT 300, 'TH',   1.5,    5,  93 UNION ALL
SELECT 100, 'TH',   -2.9,   5,  93 UNION ALL
SELECT 500, 'RT',   3.3,    5,  93 UNION ALL
SELECT 540, 'TH',   0,      5,  93 UNION ALL
SELECT 540, 'SN',   3.3,    5,  93 UNION ALL
SELECT 540, 'UL',   4.2,    5,  93 UNION ALL
SELECT 800, 'TH',   -2.4,   5,  93 UNION ALL
SELECT 300, 'RN',   2.2,    4,  35 UNION ALL
SELECT 300, 'RN',   2.5,    6,  35 UNION ALL
SELECT 100, 'RN',   1.2,    9,  35 UNION ALL
SELECT 200, 'RN',   1.2,    9,  35 UNION ALL
SELECT 300, 'RN',   1.5,    9,  35 UNION ALL
SELECT 100, 'RN',   -2.9,   9,  35 UNION ALL
SELECT 800, 'RN',   -2.4,   9,  31

我试图写一个表值UDF，它接受一个参数，@PASSIVENUMBER以及生成价值POWERTHRESHOLD和RESISTORID基础上，CAPACITORID，VOLT和WATT。

在下面的SQL代码中使用的函数。

SELECT *, CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS RESISTORID,
    CASE WHEN CAPACITORID = 'TH' AND VOLT >0 THEN (VOLT * 1000) 
    WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
    WHEN CAPACITORID <> 'TH' AND VOLT >0 THEN VOLT
    ELSE WATT END AS POWERTHRESHOLD
FROM @ELECTRONICS
WHERE PASSIVENUMBER = 93

SELECT * ,CASE WHEN RESISTORID IN ('300','800') THEN 'B03' ELSE 'B01' END AS RESISTORID,
    CASE WHEN CAPACITORID = 'RN' AND VOLT >0 THEN (VOLT * 10*3/56) 
    WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
    WHEN CAPACITORID <> 'RN' AND VOLT >10 THEN VOLT
    ELSE WATT END AS POWERTHRESHOLD
FROM @ELECTRONICS
WHERE PASSIVENUMBER = 35

当PASSIVENUMBER = 93时输出

RESISTORID  CAPACITORID VOLT    WATT    PASSIVENUMBER   RESISTORID  POWERTHRESHOLD
100 TH  1.20    5   93  A03 1200.00
200 TH  1.20    5   93  A01 1200.00
300 TH  1.50    5   93  A01 1500.00
100 TH  -2.90   5   93  A03 5.00
500 RT  3.30    5   93  A01 3.30
540 TH  0.00    5   93  A03 5000.00
540 SN  3.30    5   93  A03 3.30
540 UL  4.20    5   93  A03 4.20
800 TH  -2.40   5   93  A01 5.00

当PASSIVENUMBER = 35时输出

RESISTORID  CAPACITORID VOLT    WATT    PASSIVENUMBER   RESISTORID  POWERTHRESHOLD
300 RN  2.20    4   35  B03 2200.00
300 RN  2.50    6   35  B03 2500.00
100 RN  1.20    9   35  B01 1200.00
200 RN  1.20    9   35  B01 1200.00
300 RN  1.50    9   35  B03 1500.00
100 RN  -2.90   9   35  B01 9.00

如何实现使用case语句检查值的函数。

我尝试过的代码无法提供适当的结果。

CREATE FUNCTION [dbo].[fnchkID]
(
    @PASSIVENUMBER INT
)
RETURNS INT
AS
BEGIN
    RETURN
        CASE 
            WHEN CAPACITORID = RN and @VOLT>0 THEN VOLT * 1000
            
            ELSE WATT from @ELECTRONICS
        END 
END

请分享您的想法。

戴尔K

创建函数的文档非常详尽，因此应该以您为起点，因为他们有许多示例可以帮助您入门。

我建议使用内联表值函数，因为它们的性能通常比多语句表值函数好得多。

CREATE FUNCTION [dbo].[fnchkID]
(
    @PASSIVENUMBER INT
)
RETURNS TABLE
RETURN
    SELECT RESISTORID, CAPACITORID, VOLT, WATT, PASSIVENUMBER
        , CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS NEWRESISTORID
        , CASE WHEN PASSIVENUMBER = 93 THEN
            CASE WHEN CAPACITORID = 'TH' AND VOLT > 0 THEN (VOLT * 1000) 
            WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
            WHEN CAPACITORID <> 'TH' AND VOLT > 0 THEN VOLT
            ELSE WATT END
        WHEN PASSIVENUMBER = 35 THEN
            CASE WHEN CAPACITORID = 'RN' AND VOLT >0 THEN (VOLT * 10*3/56) 
            WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
            WHEN CAPACITORID <> 'RN' AND VOLT >10 THEN VOLT
            ELSE WATT END
        END AS POWERTHRESHOLD
    FROM @ELECTRONICS
    WHERE PASSIVENUMBER = @PASSIVENUMBER;

注意：您计算出的列不能与现有列具有相同的名称，因此不能NEWRESISTORID使用RESISTORID。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。