SQL Server Loop

SQL server hast only "while loop" concept ,there is no concept of "for loop" . A loop statement execute repeatedly until particular statement or condition reach.It is called "exit condition" or "exit point". You can specify the "exit condition" during the declaration of loop or you can specify the "exit condition" within the while condition.If you write "exit condition" within the while condition ,
you have to write "break" statement to break the loop."break" statement , break the loop , force to end the repetition work.

IF there is no "exit condition" or "exit condition" does not satisfy,the loop do repeat unending ,it is called Infinite loop.


1)Here is the Syntax of  "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE @myValue1 < @myValue2

BEGIN

{

    //Statement

    SET @myValue1 = @myValue1 + 1;

}

END;

2)Here is the example  of "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE @myValue1 < @myValue2

BEGIN

        PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

        SET @myValue1 = @myValue1 + 1;

END;

Output :

SL:0

SL:1

SL:2

SL:3

SL:4

SL:5

SL:6

SL:7

SL:8

SL:9

3)Here is the example  of "While Loop" with "break" statement

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE (1 = 1)

BEGIN

       PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

       IF (@myValue1 > @myValue2)

            BREAK;

            SET @myValue1 = @myValue1 + 1;  

END;

Output :
SL:0
SL:1
SL:2
SL:3
SL:4
SL:5
SL:6
SL:7
SL:8
SL:9
SL:10
SL:11

4)Here is the example of Nested "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 5;

DECLARE @myValue3 INT = 0;

WHILE (@myValue3<5)

    BEGIN

             PRINT '------'+CONVERT(VARCHAR,@myValue3)+'---------------';

                       WHILE (@myValue1 < @myValue2)

                       BEGIN

                                PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

                                SET @myValue1 = @myValue1 + 1;

                         END;

                  SET @myValue1=0

                  SET @myValue3 = @myValue3 + 1;

END;

5)Here is the example  of "While Loop" used in a function.

CREATE FUNCTION dbo.fn_myFunction()

RETURNS VARCHAR(50)

AS

BEGIN

          DECLARE @myValue1 INT = 0;

          DECLARE @myValue2 INT = 10;

         WHILE @myValue1 < @myValue2

        BEGIN

        SET @myValue1 = @myValue1 + 1;

        END;

                     RETURN CONVERT(VARCHAR,@myValue1 )

END

Execute function

SELECT dbo.fn_myFunction()

Output :

10

6)Infinite Loop

WHILE (1=1)

BEGIN

    PRINT 'Loop is executing';

END;

Output :
Loop is executing
Loop is executing
Loop is executing
..
..
..