Do while loop in SQL Server 2008

Question

Is there any method for implement do while loop in SQL server 2008

User · Answer

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 FOR
SELECT Id, Title FROM dbo.SourceTable
OPEN Iterator
WHILE 1=1 BEGIN
    FETCH NEXT FROM @InputTable INTO @Id, @Title
    IF @@FETCH_STATUS < 0 BREAK
    PRINT 'Do something with ' + @Title
END
CLOSE Iterator
DEALLOCATE Iterator

Unfortunately, T-SQL doesn't seem to offer a cleaner way to singly-define the loop operation, than this infinite loop.

User · Answer

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 1 DO  PRINT I  SET I I 1 WHILE I lt  10   Here is the equivalent T-SQL code using goto   DECLARE  I INT 1  START                 -- DO   PRINT  I    SET  I  1  IF  I lt  10 GOTO START  -- WHILE  I lt  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 lt  10  BREAK  -- WHILE  I lt  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 1 REPEAT   PRINT I   SET I I 1 UNTIL I gt 10       and the GOTO based solution in T-SQL   DECLARE  I INT 1  START                     -- REPEAT   PRINT  I    SET  I  1  IF NOT  I gt 10  GOTO START  -- UNTIL  I gt 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 gt 10 BREAK  -- UNTIL  I gt 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

User · Answer

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 INT SET  intFlag   1 WHILE   intFlag  lt  5  BEGIN     PRINT  intFlag     SET  intFlag    intFlag   1 END GO       ResultSet   1 2 3 4 5    Example of WHILE Loop with BREAK keyword  DECLARE  intFlag INT SET  intFlag   1 WHILE   intFlag  lt  5  BEGIN     PRINT  intFlag     SET  intFlag    intFlag   1     IF  intFlag   4         BREAK  END GO       ResultSet   1 2 3    Example of WHILE Loop with CONTINUE and BREAK keywords  DECLARE  intFlag INT SET  intFlag   1 WHILE   intFlag  lt  5  BEGIN     PRINT  intFlag     SET  intFlag    intFlag   1     CONTINUE      IF  intFlag   4 -- This will never executed         BREAK  END GO       ResultSet   1 2 3 4 5       But try to avoid loops at database level  Reference

User · Answer

You can also use an exit variable if you want your code to be a bit more readable   DECLARE  Flag int   0 DECLARE  Done bit   0  WHILE  Done   0 BEGIN     SET  Flag    Flag   1     PRINT  Flag     IF  Flag  gt   5 SET  Done   1 END   This would probably be more relevant when you have a more complicated loop and are trying to keep track of the logic  As stated loops are expensive so try and use other methods if you can

User · Answer

Only While Loop is officially supported by SQL server  Already there is answer for DO while loop  I am detailing answer on ways to achieve different types of loops in SQL server   If you know  you need to complete first iteration of loop anyway  then you can try DO  WHILE or REPEAT  UNTIL version of SQL server   DO  WHILE Loop  DECLARE  X INT 1   WAY   -- gt  Here the  DO statement    PRINT  X     SET  X    1   IF  X lt  10 GOTO WAY    REPEAT  UNTIL Loop  DECLARE  X INT   1   WAY   -- Here the REPEAT statement    PRINT  X     SET  X    1   IFNOT  X  gt  10  GOTO WAY    FOR Loop  DECLARE  cnt INT   0   WHILE  cnt  lt  10 BEGIN    PRINT  Inside FOR LOOP      SET  cnt    cnt   1  END   PRINT  Done FOR LOOP     Reference

[sql-server] Do while loop in SQL Server 2008

Examples related to sql-server

Examples related to sql-server-2008

Examples related to loops

Examples related to while-loop

Examples related to do-while