If Else If 在 Sql Server 函数中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5031748/
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
If Else If In a Sql Server Function
提问by salim
I have this function I am trying to create. When I parse it, it works fine, but to actually create the function in the database it says my column names are invalid. That is not true, I spelled them correctly. Here is the code:
我有我正在尝试创建的功能。当我解析它时，它工作正常，但要在数据库中实际创建该函数，它会说我的列名无效。这不是真的，我拼写正确。这是代码：
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @Final nvarchar
IF EXISTS (
SELECT
question,
yes_ans,
no_ans,
na_ans,
blank_ans
FROM dbo.qrc_maintally
WHERE school_id = @SchoolId
)
IF yes_ans > no_ans AND yes_ans > na_ans
BEGIN
SET @Final = 'Yes'
END
ELSE IF no_ans > yes_ans AND no_ans > na_ans
BEGIN
SET @Final = 'No'
END
ELSE IF na_ans > yes_ans AND na_ans > no_ans
BEGIN
SET @Final = 'N/A'
END
RETURN @Final
END
回答by Joel Coehoorn
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS nvarchar(3)
AS BEGIN
DECLARE @Final nvarchar(3)
SELECT @Final = CASE
WHEN yes_ans > no_ans AND yes_ans > na_ans THEN 'Yes'
WHEN no_ans > yes_ans AND no_ans > na_ans THEN 'No'
WHEN na_ans > yes_ans AND na_ans > no_ans THEN 'N/A' END
FROM dbo.qrc_maintally
WHERE school_id = @SchoolId
Return @Final
End
As you can see, this simplifies the code a lot. It also makes other errors in your code more obvious: you're returning an nvarchar, but declared the function to return an int (corrected in the code above).
如您所见，这大大简化了代码。它还使您的代码中的其他错误更加明显：您返回的是 nvarchar，但声明了返回 int 的函数（在上面的代码中已更正）。
回答by Joe Stefanelli
You'll need to create local variables for those columns, assign them during the select and use them for your conditional tests.
您需要为这些列创建局部变量，在选择期间分配它们并将它们用于条件测试。
declare @yes_ans int,
@no_ans int,
@na_ans int
SELECT @yes_ans = yes_ans, @no_ans = no_ans, @na_ans = na_ans
from dbo.qrc_maintally
where school_id = @SchoolId
If @yes_ans > @no_ans and @yes_ans > @na_ans
begin
Set @Final = 'Yes'
end
-- etc.
回答by RichardTheKiwi
No one seems to have picked that if (yes=no)>na or (no=na)>yes or (na=yes)>no, you get NULL as the result. Don't believe this is what you are after.
似乎没有人选择如果 (yes=no)>na 或 (no=na)>yes 或 (na=yes)>no，结果为 NULL。不要相信这就是你所追求的。
Here's also a more condensed form of the function, which works even ifany of yes, no or na_ans is NULL.
这里还有一个更简洁的函数形式，即使yes、no 或 na_ans 中的任何一个为 NULL ，它也能工作。
USE [***]
GO
/****** Object: UserDefinedFunction [dbo].[fnActionSq] Script Date: 02/17/2011 10:21:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS nvarchar(3)
AS
BEGIN
return (select (
select top 1 Result from
(select 'Yes' Result, yes_ans union all
select 'No', no_ans union all
select 'N/A', na_ans) [ ]
order by yes_ans desc, Result desc)
from dbo.qrc_maintally
where school_id = @SchoolId)
End
回答by mpickens
I think you'd be better off with a CASE statement, which works a lot more like IF/ELSEIF
我认为你最好使用 CASE 语句，它更像 IF/ELSEIF
DECLARE @this int, @value varchar(10)
SET @this = 200
SET @value = (
SELECT
CASE
WHEN @this between 5 and 10 THEN 'foo'
WHEN @this between 10 and 15 THEN 'bar'
WHEN @this < 0 THEN 'barfoo'
ELSE 'foofoo'
END
)
More info: http://technet.microsoft.com/en-us/library/ms181765.aspx
更多信息：http: //technet.microsoft.com/en-us/library/ms181765.aspx
回答by Amy B
If yes_ans > no_ans and yes_ans > na_ans
You're using column names in a statement (outside of a query). If you want variables, you must declare and assign them.
您在语句中使用列名（在查询之外）。如果需要变量，则必须声明并分配它们。
回答by pelazem
Look at these lines:
看看这些行：
If yes_ans > no_ans and yes_ans > na_ans
如果 yes_ans > no_ans 和 yes_ans > na_ans
and similar. To what do "yes_ans" etc. refer? You're not using these in the context of a query; the "if exists" condition doesn't extend to the column names you're using inside.
和类似的。“yes_ans”等指的是什么？你不是在查询的上下文中使用这些；“如果存在”条件不会扩展到您在内部使用的列名。
Consider assigning those values to variables you can then use for your conditional flow below. Thus,
考虑将这些值分配给变量，然后您可以将其用于下面的条件流。因此，
if exists (some record)
begin
set @var = column, @var2 = column2, ...
if (@var1 > @var2)
-- do something
end
结尾
The return type is also mismatched with the declaration. It would help a lot if you indented, used ANSI-standard punctuation (terminate statements with semicolons), and left out superfluous begin/end - you don't need these for single-statement lines executed as the result of a test.
返回类型也与声明不匹配。如果您缩进、使用 ANSI 标准标点符号（用分号终止语句），并省略多余的开始/结束，这将有很大帮助 - 作为测试结果执行的单语句行不需要这些。
