CODEWITHSUNDEEP
sqlsql-servert-sql
taki Martillo
taki Martillo

Reputation: 406

While loop with multiple conditions in T-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.

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.

Right now, I've just got

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

Upvotes: 2

Views: 16190

Answers (2)

taki Martillo
taki Martillo

Reputation: 406

I feel like a complete idiot. And I'm sure I look like one too.

I tried all that stuff, trying to get the condition to work. I just could not figure out why it wasn't working.

It just hit me. I was changing the code while I was debugging it, and it wasn't executing my changes.

I'm pretty embarrassed. I was doing it right after all. I was just... doing it wrong.

Upvotes: 0

ceth
ceth

Reputation: 45295

You must look at declaration of WHILE statement:

WHILE Boolean_expression 
     { sql_statement | statement_block | BREAK | CONTINUE }

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

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

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

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

Upvotes: 4

Related Questions