如何在 SQL 中的标量值函数中使用 case 语句? [英] How to use a case statement in scalar valued function in SQL?

问题描述

我想使用 case 语句从函数中获取一个值.我尝试了以下但它不起作用:

I want to get one value from a function using a case statement. I tried the following but it does not work:

CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(

    @Period INT

)
RETURNS int
AS
BEGIN

    SELECT CASE @Period 
             when 1 then 1
             when @Period >1 and @Period <=7 then 1
             when @Period >7 and @Period <=30 then 1
             when @Period >30 and @Period<=90 then 1
             when @Period >90 and @Period <=180 then 1
             when @Period >180 and @Period <=360 then 1
             else 0
           END

    RETURN @Period
END

推荐答案

CASE 表达式:简单和搜索.您必须选择其中之一 - 您不能在一个表达式中混合使用这两种类型.

There are two types of CASE expression: simple and searched. You must choose one or the other - you can't use a mixture both types in one expression.

试试这个:

SELECT CASE
    WHEN @Period = 1 THEN 1
    WHEN @Period > 1 AND @Period <= 7 THEN 2
    WHEN @Period > 7 AND @Period <= 30 then 3
    -- etc...
    ELSE 0
END

此外，您需要将结果分配给其他人已经指出的内容.

Also, you need to assign the result to something as others have already pointed out.

这篇关于如何在 SQL 中的标量值函数中使用 case 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！