• While Loop with Break and Continue keywords & GOTO statement in SQL Server

    Posted on March 27, 2012 by in Database, SQL Server

    I have seen so many requests by so many users asking for WHILE LOOP construct.  It is as simple as writing a while loop in any programming language.  While loop sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

     

    Syntax:

    WHILE Boolean_expression 
         { sql_statement | statement_block } 
         [ BREAK ] 
         { sql_statement | statement_block } 
         [ CONTINUE ] 
         { sql_statement | statement_block }

     

    EXAMPLE 1:

    A simple while loop printing all the numbers from 1 to 5.

    DECLARE @index INT
     SET @index = 1
     WHILE (@index <= 5)
     BEGIN
     PRINT @index
     SET @index = @index + 1
     END
     GO

    Output
    1
    2
    3
    4
    5
     

    EXAMPLE 2:

    A simple while loop with break keyword

    DECLARE @index INT
     SET @index = 1
     WHILE (@index <= 5)
     BEGIN
     IF(@index = 3)
     -- when index value reaches 3, control breaks from the loop
     BREAK; 
     PRINT @index
     SET @index = @index + 1
     END
     GO
    

    Output
    1
    2
     

    EXAMPLE 3:

    A simple while loop with continue keyword

    DECLARE @index INT
     SET @index = 0&nbsp;
     WHILE (@index <= 5)
     BEGIN 
     SET @index = @index + 1
     IF(@index = 4)
     CONTINUE; -- WHEN INDEX VALUE IS 4, IT SKIPS PRINTING 4&nbsp;
     PRINT @index 
     END
     GO

    Output
    1
    2
    3
    5
    6
     

    EXAMPLE 4:

    A simple while loop with break and continue keyword

    DECLARE @index INT
     SET @index = 0
     WHILE (@index <= 5)
     BEGIN 
     SET @index = @index + 1
     IF(@index = 2)
     CONTINUE; -- WHEN INDEX VALUE IS 2, IT SKIPS PRINTING 2 &nbsp;
     PRINT @index &nbsp;
     IF(@index = 4)
     BREAK; -- WHEN INDEX VALUE IS 4, BREAK FROM THE LOOP 
     END
     GO

    Output
    1
    3
    4
     

    EXAMPLE 5:

    A simple while look with “GOTO” statement

    DECLARE @Counter int;
    SET @Counter = 1;
    WHILE @Counter < 10
    BEGIN 
        PRINT @Counter
        SET @Counter = @Counter + 1
        IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
        IF @Counter = 5 GOTO Branch_Two  --This will never execute.
    END
    Branch_One:
        SELECT 'Jumping To Branch One.'
        GOTO Branch_Three; --This will prevent Branch_Two from executing.
    Branch_Two:
        SELECT 'Jumping To Branch Two.'
    Branch_Three:
        SELECT 'Jumping To Branch Three.'

     

    EXAMPLE 6:

    Using while in a simple cursor

    --CREATE TABLE DEPARTMENTS
    CREATE TABLE [dbo].[tblDepartments](
     [DepartmentID] [int] NOT NULL,
     [DepartmentName] [varchar](255) NOT NULL,
     [DepartmentCode] [varchar](255) NOT NULL,
     [IsActive] [bit] NOT NULL,
     [CreatedDate] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO
    --INSERT SOME SAMPLE DATA
    INSERT INTO [dbo].[tblDepartments]  
     VALUES
               (1,'Dept 1','Dept Code 1',1,GetDate())
    GO           
    INSERT INTO [dbo].[tblDepartments]
     VALUES
               (2,'Dept 2','Dept Code 2',1,GetDate())           
    GO
    
    --DECLARE A CURSOR
    DECLARE Dept_Cursor CURSOR FOR
    SELECT D.DepartmentID, D.DepartmentName
    FROM tblDepartments D
    Print 'Department ID ' + 'Department Name'
    DECLARE @DepartmentID INT, @DepartmentName varchar(255)
    OPEN Dept_Cursor;
    --FETCH FIRST RECORD
    FETCH NEXT FROM Dept_Cursor INTO @DepartmentID, @DepartmentName;
    --KEEP FETCHING UNTIL WE THERE ARE NO RECORDS LEFT
    WHILE @@FETCH_STATUS = 0
       BEGIN
     PRINT RIGHT(convert(varchar, @DepartmentID) 
     + Space(13), 14) 
     + @DepartmentName 
     --FETCH NEXT RECORD
     FETCH NEXT FROM Dept_Cursor INTO @DepartmentID, @DepartmentName;
       END;
    CLOSE Dept_Cursor;
    DEALLOCATE Dept_Cursor;
    GO

    Output

    Department ID Department Name
    1                          Dept 1
    2                          Dept 2

    Be Sociable, Share!
      Post Tagged with , , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply