Do while loop in SQL Server 2008
I am not sure about DO-WHILE IN MS SQL Server 2008 but you can change your WHILE loop logic, so as to USE like DO-WHILE loop.
Examples are taken from here: http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/
Example of WHILE Loop
DECLARE @intFlag INTSET @intFlag = 1WHILE (@intFlag <=5)BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1ENDGO
ResultSet:
12345
Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INTSET @intFlag = 1WHILE (@intFlag <=5)BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 IF @intFlag = 4 BREAK;ENDGO
ResultSet:
123
Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INTSET @intFlag = 1WHILE (@intFlag <=5)BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 CONTINUE; IF @intFlag = 4 -- This will never executed BREAK;ENDGO
ResultSet:
12345
But try to avoid loops at database level.Reference.
If you are not very offended by the GOTO
keyword, it can be used to simulate a DO
/ WHILE
in T-SQL. Consider the following rather nonsensical example written in pseudocode:
SET I=1DO PRINT I SET I=I+1WHILE I<=10
Here is the equivalent T-SQL code using goto:
DECLARE @I INT=1;START: -- DO PRINT @I; SET @I+=1;IF @I<=10 GOTO START; -- WHILE @I<=10
Notice the one to one mapping between the GOTO
enabled solution and the original DO
/ WHILE
pseudocode. A similar implementation using a WHILE
loop would look like:
DECLARE @I INT=1;WHILE (1=1) -- DO BEGIN PRINT @I; SET @I+=1; IF NOT (@I<=10) BREAK; -- WHILE @I<=10 END
Now, you could of course rewrite this particular example as a simple WHILE
loop, since this is not such a good candidate for a DO
/ WHILE
construct. The emphasis was on example brevity rather than applicability, since legitimate cases requiring a DO
/ WHILE
are rare.
REPEAT / UNTIL, anyone (does NOT work in T-SQL)?
SET I=1REPEAT PRINT I SET I=I+1UNTIL I>10
... and the GOTO
based solution in T-SQL:
DECLARE @I INT=1;START: -- REPEAT PRINT @I; SET @I+=1;IF NOT(@I>10) GOTO START; -- UNTIL @I>10
Through creative use of GOTO
and logic inversion via the NOT
keyword, there is a very close relationship between the original pseudocode and the GOTO
based solution. A similar solution using a WHILE
loop looks like:
DECLARE @I INT=1;WHILE (1=1) -- REPEAT BEGIN PRINT @I; SET @I+=1; IF @I>10 BREAK; -- UNTIL @I>10 END
An argument can be made that for the case of the REPEAT
/ UNTIL
, the WHILE
based solution is simpler, because the if condition is not inverted. On the other hand it is also more verbose.
If it wasn't for all of the disdain around the use of GOTO
, these might even be idiomatic solutions for those few times when these particular (evil) looping constructs are necessary in T-SQL code for the sake of clarity.
Use these at your own discretion, trying not to suffer the wrath of your fellow developers when they catch you using the much maligned GOTO
.
I seem to recall reading this article more than once, and the answer is only close to what I need.
Usually when I think I'm going to need a DO WHILE
in T-SQL it's because I'm iterating a cursor, and I'm looking largely for optimal clarity (vs. optimal speed). In T-SQL that seems to fit a WHILE TRUE
/ IF BREAK
.
If that's the scenario that brought you here, this snippet may save you a moment. Otherwise, welcome back, me. Now I can be certain I've been here more than once. :)
DECLARE Id INT, @Title VARCHAR(50)DECLARE Iterator CURSOR FORWARD_ONLY FORSELECT Id, Title FROM dbo.SourceTableOPEN IteratorWHILE 1=1 BEGIN FETCH NEXT FROM @InputTable INTO @Id, @Title IF @@FETCH_STATUS < 0 BREAK PRINT 'Do something with ' + @TitleENDCLOSE IteratorDEALLOCATE Iterator
Unfortunately, T-SQL doesn't seem to offer a cleaner way to singly-define the loop operation, than this infinite loop.