While loop with multiple conditions in T-SQL

Question

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

WHILE loops are totally valid to use as long as they're not being used out of ignorance of set based operations. — Nick.Mc
– Nick.Mc, Commented Dec 11, 2014 at 3:24

ceth · Accepted Answer · 2014-12-11 04:20:24Z

4

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.

answered Dec 11, 2014 at 4:20

ceth

45.5k63 gold badges190 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

taki Martillo Over a year ago

I know. I was confused because it wasn't working, and it wasn't working because I was changing it in debug mode.

Noumenon Over a year ago

Since your IF statement has an END, shouldn't it have a BEGIN? Or omit the END?

taki Martillo · Accepted Answer · 2014-12-11 06:39:08Z

0

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.

answered Dec 11, 2014 at 6:39

taki Martillo

4063 gold badges9 silver badges23 bronze badges

Collectives™ on Stack Overflow

While loop with multiple conditions in T-SQL

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related