我可以在 WHILE 循环中使用 CASE 语句吗?

发布于 2024-09-03 06:23:00 字数 391 浏览 8 评论 0原文

这就是我正在做的:

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

我收到此错误:

关键字“CASE”附近的语法不正确。

不知道为什么。请帮忙!

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'.

No idea why. Please help!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

回眸一遍 2024-09-10 06:23:00

不,SQL中的CASE结构是为了返回一个值,而不是为了程序流程。您需要将其分解为 IF 语句。

No, the CASE structure in SQL is to return a value, not for program flow. You need to break it into IF statements.

海风掠过北极光 2024-09-10 06:23:00

对于您所提议的内容,您应该使用 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

For what you are proposing, you should use IF statements

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
蓝海似她心 2024-09-10 06:23:00

CASE 语句不像过程代码中的同类语句那样用于分支逻辑。它将返回结果集中的结果,以便当您将值分配给变量时,您可以确定该值是什么而不是分配给哪个变量。

不幸的是,

(@monFee1 = @counter, @newBalance = @newBalance-@fee)

不返回值。

使用 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

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.

Unfortunately,

(@monFee1 = @counter, @newBalance = @newBalance-@fee)

doesn't return a value.

An alternate approach using If/Else bracnching logic would be

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
思念满溢 2024-09-10 06:23:00

你不应该在这里使用 while 循环。您实际上只检查了一次 @newBalance 的值。考虑:

@monFee1 = @monthlyFee
@newBalance = @newBalance-@fee

IF @newBalance > 0
BEGIN
   @monFee2 = @monthlyFee
   @newBalance = @newBalance-@fee
END 

You shouldn't use a while loop here. You are effectively only checking the value of @newBalance one time. Consider:

@monFee1 = @monthlyFee
@newBalance = @newBalance-@fee

IF @newBalance > 0
BEGIN
   @monFee2 = @monthlyFee
   @newBalance = @newBalance-@fee
END 
若相惜即相离 2024-09-10 06:23:00

由于 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

它也可以在 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:祝你的衰老报告好运......

Since CASE is an expression, you can use it within a SET assignment statement.

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.

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...

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文