How to implement a do-while loop in tsql

Question

I'm trying to figure how to implement this in TSQL

do 
  update stuff set col = 'blah' where that_row = 'the right one'
  select trash from stuff ...
until some_condition

The only iterative control flow sentence provided by Transact-SQL is while (condition) sentences that first evaluates the condition and if that condition is true then execute the sentence.

I'm thinking in a scenario like execute a UPDATE statement over a table until some condition triggered y the last UPDATE executed is achieved.

Most important, I'm looking for the less-dirty approach to this problem (Duplicating the UPDATE before the WHILE does not make too much sense to me as the UPDATE sentence can be arbitrarily long and complex)

EDIT: The problem I'm trying to solve involve multiple UPDATE statements under the same table, each one taking and transforming the values from the previous iterations. This is not possible to do in just one big UPDATE statement, as each row will be evaluated and updated only once, so a loop is the only way I can figure out to make this mess to work.

If you're thinking flow-control for database code, you're doing it wrong. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 3, 2009 at 3:45
Don't be so inflexible, sometimes business logic needs to be developed in stored procedures. — Rodrigo
– Rodrigo, Commented Oct 3, 2009 at 3:53
@rodrigo, he isn;talking about it being a stored proc, he is talking about using set theory and not a looop at all. It is possible to avoid the loop about 99% of teh time and it is ALWAYS preferable to do so if you can. — HLGEM
– HLGEM, Commented Jan 30, 2015 at 21:42
@HLGEM did you read the EDIT section of my question? you're right, 99% of the time a properly build query solves everything, i was asking because some times you have to deal with a case in that 1%... — Rodrigo
– Rodrigo, Commented Feb 12, 2015 at 17:19

Mitch Wheat · Accepted Answer · 2022-08-13 01:16:20Z

48

This is effectively a Do-While loop:

WHILE (1=1)
BEGIN
  
  -- Do stuff...

  IF (some_condition is true)
     BREAK;

END

But as @Joel Coehoorn noted, always try to use a set based approach first. I would only resort to a loop if I can't think of a way to solve using set operations.

edited Aug 13, 2022 at 1:16

answered Oct 3, 2009 at 3:46

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

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

2 Comments

Rodrigo Over a year ago

1 is not a boolean value, instead of this (1=1) can do the job. This solution works and is very simple. Seems obvious after reading! Thanks a lot.

Cody Konior Over a year ago

Just wanted to add that the () for while isn't required unless it was a select of some kind.

Collectives™ on Stack Overflow

How to implement a do-while loop in tsql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related