在 T-SQL 中具有多个条件的 while 循环

2021-09-10 00:00:00 sql tsql sql-server

首先让我说我知道我知道这种循环很可怕，你不应该在 Transact SQL 中使用它们.但是，出于某些目的(这些目的无关紧要，所以不要问我你想做什么！?")你只需要这样做.我不想，但我必须.

Let me start out by saying I know I KNOW that these kind of loops are horrible and you shouldn't use them in Transact SQL. But, for some purposes (those purposes being irrelevant so don't ask me "what're you trying to do!?") ya just have to. I don't want to, but I gotta.

无论如何.有没有办法让 T-SQL 中的 while 循环在复杂的条件语句上终止?就像，在 C# 中，我只想说 while (i > -10 && i <10) ，因为我希望循环在标记值介于 -10 和 10 之间时终止，但我只是...可以不知道怎么做.

Anyway. Is there some way to have a while loop in T-SQL terminate on a complex conditional statement? like, in C# I'd just say while (i > -10 && i < 10) , because I want the loop to terminate when the sentinel value is between -10 and 10, but I just... can't figure out how to do it.

这可能非常简单……或者……不可能.请指教.

It's probably excruciatingly simple... or.. impossible. Please advise.

现在，我刚刚得到

WHILE @N <> 0
BEGIN
   --code and such here
END

推荐答案

你必须看WHILE语句的声明:

You must look at declaration of WHILE statement:

WHILE Boolean_expression 
     { sql_statement | statement_block | BREAK | CONTINUE }

首先，你可以像 Dan 所说的那样使用复杂的 Boolean_expression:

First of all you can use complex Boolean_expression as Dan said:

WHILE @N > -1 AND @N <10
BEGIN
END

如果您想为代码添加更多灵活性，可以使用 IF 与 BREAK，类似这样:

If you want to add more flexibility to you code you can use IF with BREAK, something like this:

WHILE @N > -1 AND @N <10
BEGIN

  -- code
  IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
    BREAK
  END
  -- code

END

退出循环或使用 CONTINUE 跳过一个循环.

to go out of cycle or use CONTINUE to skip one cycle.

相关文章