如何在 SQL 中的标量值函数中使用 case 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5070153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a case statement in scalar valued function in SQL?
提问by HAJJAJ
I want to get one value from a function using a case statement. I tried the following but it does not work:
我想使用 case 语句从函数中获取一个值。我尝试了以下但它不起作用：
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
回答by Mark Byers
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.
CASE 表达式有两种类型：简单和搜索。您必须选择其中之一 - 您不能在一个表达式中混合使用这两种类型。
Try this:
尝试这个：
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.
此外，您需要将结果分配给其他人已经指出的内容。
回答by bernd_k
When you use RETURN @Period, you must assign a value to @Period. The following example shows how to structure your code so that there is no need to declare a local variable.
使用 RETURN @Period 时，必须为 @Period 赋值。以下示例显示了如何构建代码以便无需声明局部变量。
CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(
@Period INT
)
RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN @Period = 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
END
回答by Neil Knight
Declare a second variable and then set that value because you aren't resetting @Period.
声明第二个变量，然后设置该值，因为您没有重置@Period.
For example:
例如：
DECLARE @Output AS INT
SELECT @Output = CASE @Period
WHEN 1 then 1
WHEN @Period > 1 AND @Period <= 7 THEN 1 -- Should be 2
WHEN @Period > 7 AND @Period <= 30 THEN 1 -- Should be 3
WHEN @Period > 30 AND @Period<= 90 THEN 1 -- Should be 4
WHEN @Period > 90 AND @Period <= 180 THEN 1 -- Should be 5
WHEN @Period > 180 AND @Period <= 360 THEN 1 -- Should be 6
ELSE 0 END;
RETURN @Output;
I have left it like this as I'm assuming you are going to change your values for each of these CASEstatements.
我将它保留为这样，因为我假设您将更改每个CASE语句的值。
回答by Dan J
In what way does it "not work"? Does it produce an error when you try to execute it? Does it return an unexpected result?
它以什么方式“不起作用”？当您尝试执行它时是否会产生错误？它是否返回意外结果？
Off the top of my head, there are several problems:
在我的头顶上，有几个问题：
Your case statement returns 1in all cases.
您的 case 语句1在所有情况下都会返回。
The case statement is also combining two different types of CASEsemantics:
case 语句还结合了两种不同类型的CASE语义：
CASE @Period
WHEN <condition> THEN <result>
WHEN <other condition> THEN <result>
ELSE <result>
END
or
或者
CASE
WHEN @Period = <value> THEN <result>
WHEN @Period = <other value> THEN <result>
ELSE <result>
END
The second form allows you to use unrelated conditions, whereas the first can only check for different values of @Period.
第二种形式允许您使用不相关的条件，而第一种形式只能检查 的不同值@Period。
Further, you're returning the value of @Period, not the value generated by the CASE statement.
此外，您返回的是@Period 的值，而不是 CASE 语句生成的值。
回答by PedroC88
You are not assigning @Period anywhere. Try with when @Period >1 and @Period <=7 then SET @Period = 1and subsequently for all the other lines.
您没有在任何地方分配 @Period。尝试使用when @Period >1 and @Period <=7 then SET @Period = 1和 随后用于所有其他行。
SQL does not have implicit returns and so you always have to set the variables before you return them.
SQL 没有隐式返回，因此您始终必须在返回之前设置变量。
回答by RichardTheKiwi
Here's a SET based approach to write your function in SQL Server 2008
这是在 SQL Server 2008 中编写函数的基于 SET 的方法
CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(
@Period INT
)
RETURNS int
AS
BEGIN
return isnull((SELECT min(v)
from (values
(1,1),
(7,2),
(30,3),
(90,4),
(180,5),
(360,6)) t(n,v)
where n >= @Period and @period>0), 0)
END
The way you have written it, all the CASE branches return 1, so you might as well use
按照你写的方式，所有的 CASE 分支都返回 1，所以你不妨使用
return case when @period between 1 and 360 then 1 else 0 end
