SQL 我可以在 WHILE 循环中使用 CASE 语句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2915883/
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
Can I have a CASE statement within a WHILE loop?
提问by John
This is what I'm doing:
这就是我正在做的：
while (@counter < 3 and @newBalance >0)
begin
CASE
when @counter = 1 then ( @monFee1 = @monthlyFee, @newBalance = @newBalance-@fee)
when @counter = 2 then ( @monFee2 = @monthlyFee, @newBalance = @newBalance-@fee)
END
@counter = @counter +1
end
I get this error:
我收到此错误：
Incorrect syntax near the keyword 'CASE'.
关键字“CASE”附近的语法不正确。
No idea why. Please help!
不知道为什么。请帮忙！
回答by Thomas
For what you are proposing, you should use IF statements
对于您提出的建议，您应该使用 IF 语句
While (@counter < 3 and @newBalance >0)
Begin
If @Counter = 1 Then
Begin
Set @monFee1 = @monthlyFee
Set @newBalance = @newBalance-@fee
End
If @Counter = 2 Then
Begin
Set @monFee2 = @monthlyFee
Set @newBalance = @newBalance-@fee
End
Set @counter = @counter +1
End
回答by Tom H
No, the CASE structure in SQL is to return a value, not for program flow. You need to break it into IF statements.
不，SQL 中的 CASE 结构是为了返回一个值，而不是为了程序流程。您需要将其分解为 IF 语句。
回答by Laramie
The CASE statement isn't used for branching logic in the same way as its cousin in procedural code. It will return a result in a result set so that when your assigning a value to a variable, you can determine what the value will be not which variable you are assigning to.
CASE 语句不像它在过程代码中的表亲那样用于分支逻辑。它将在结果集中返回一个结果，以便在为变量分配值时，您可以确定该值将是什么而不是您要分配给哪个变量。
Unfortunately,
很遗憾，
(@monFee1 = @counter, @newBalance = @newBalance-@fee)
doesn't return a value.
不返回值。
An alternate approach using If/Else bracnching logic would be
使用 If/Else 分支逻辑的另一种方法是
while (@counter < 3 and @newBalance >0)
begin
IF @counter = 1
THEN
SET @monFee1 = @monthlyFee
SET @newBalance = @newBalance-@fee
END
ELSE IF @counter = 2
BEGIN
SET @monFee2 = @monthlyFee
SET @newBalance = @newBalance-@fee
END
SET @counter = @counter +1
end
回答by Tim Rooks
You shouldn't use a while loop here. You are effectively only checking the value of @newBalance one time. Consider:
你不应该在这里使用 while 循环。您实际上只检查一次 @newBalance 的值。考虑：
@monFee1 = @monthlyFee
@newBalance = @newBalance-@fee
IF @newBalance > 0
BEGIN
@monFee2 = @monthlyFee
@newBalance = @newBalance-@fee
END
回答by Amy B
Since CASE is an expression, you can use it within a SET assignment statement.
由于 CASE 是一个表达式，您可以在 SET 赋值语句中使用它。
WHILE (@counter < 3 and @newBalance >0)
BEGIN
SET @monFee1 = CASE WHEN @Counter=1
THEN @monthlyFee ELSE @monFee1 END
SET @monFee2 = CASE WHEN @Counter=2
THEN @monthlyFee ELSE @monFee2 END
SET @newBalance = @newBalance - CASE WHEN @Counter in (1, 2)
THEN @fee ELSE 0 END
SET @counter = @counter +1
END
It can also within a SELECT assignment statement.
它也可以在 SELECT 赋值语句中。
WHILE (@counter < 3 and @newBalance >0)
BEGIN
SELECT
@monFee1 = CASE WHEN @Counter=1
THEN @monthlyFee ELSE @monFee1 END,
@monFee2 = CASE WHEN @Counter=2
THEN @monthlyFee ELSE @monFee2 END,
@newBalance = @newBalance - CASE WHEN @Counter in (1, 2)
THEN @fee ELSE 0 END,
@counter = @counter +1
END
PS: good luck with your aging report...
PS：祝你的老化报告好运......
