SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords

SQL SERVER - Simple Example of WHILE Loop With CONTINUE and BREAK Keywords break This question is one of those questions which is very simple and most of the users get it correct, however few users find it confusing for the first time. I have tried to explain the usage of simple WHILE loop in the first example. BREAK keyword will exit the stop the while loop and control is moved to the next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to the first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example for CONTINUE and BREAK keywords.

1) Example of WHILE Loop

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

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

Result

Set:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

Result Set:

1
2
3
4
5

I hope you find this simple blog post helpful. Let me know what you think of this blog post. Please leave your views in the comment sections.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Difference Between @@Version and xp_msver – Retrieve SQL Server Information
Next Post
SQL SERVER – 2005 – Forced Parameterization and Simple Parameterization – T-SQL and SSMS

Related Posts

182 Comments. Leave new

  • how to use loop inside my procedure for all month dates..
    my procedure is:

    alter procedure dateWiseAttnd
    as
    begin
    select Employee,
    max(case when day(att_date)=’01’ then Attendance_Status else ” end) ‘day1′,
    max(case when day(att_date)=’02’ then Attendance_Status else ” end) ‘day2′,
    max(case when day(att_date)=’03’ then Attendance_Status else ” end) ‘day3′,
    max(case when day(att_date)=’04’ then Attendance_Status else ” end) ‘day4′,
    max(case when day(att_date)=’05’ then Attendance_Status else ” end) ‘day5′,
    max(case when day(att_date)=’06’ then Attendance_Status else ” end) ‘day6′,
    max(case when day(att_date)=’07’ then Attendance_Status else ” end) ‘day7′,
    max(case when day(att_date)=’08’ then Attendance_Status else ” end) ‘day8′
    from
    (select e.EMP_FIRST_NAME+’ ‘+e.EMP_LAST_NAME Employee,a.Attendance_Status,a.att_date
    from attendance_report a,EMPLOYEE_MASTER e
    where MONTH(a.att_date)=’07’
    and YEAR(a.att_date)=’2011′
    and a.emp_id=e.ID
    )tbl
    group by Employee
    end

    Reply
  • Pinal Dave thanks a lot for the example..

    Fernando

    from Bolivia

    Reply
  • how to print * using sql query(traingle shape)
    please give query here

    Reply
  • Hi Pinal,

    I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
    My question is how to insert 400,000 rows at a time in a table.
    Can you let me know the script to generate it.

    Please do help me out.

    Thanks in Advance.

    Reply
  • Hello Sir,

    I have Temp table with the column(rec_seq,acc_id,tax_id,zip,party_name,addr_line1,addr_line2) and I want to display table with different columns in each row like :
    First row : rec_seq,acc_id,tax_id,zip (wehre rec_seq =1)
    Second row : party_name,addr_line1,addr_line2 (where rec_seq=2)
    and store this result into another table
    How can I do that..

    please do help me out.

    Thanks in advance.

    Reply
  • Hello Sir,

    I have one table named “Account” and fields are(rec_seq,acc_id,security_key,party_name,party_addr_line1) I want to display different coulmns in each row :

    output should be :

    First row :
    rec_seq,acc_id,security_key (where rec_seq=1 i.e. display selected column when rec_seq =1 )

    Second row:
    rec_seq,party_name,party_addr_line1,party_addr_line2 (where rec_seq=2i.e. display selected column when rec_seq =2)

    Right now Account table data look like below:

    rec_seq acc_id security_key party_name party_addr_line1
    1 10001 1 NULL NULL
    1 10002 2 NULL NULL
    2 10002 NULL b ab
    1 10002 3 NULL NULL
    2 10002 NULL b bc
    2 10002 NULL jk lm

    I want to fetch data from this table and display output like :
    (rec_seq=1 so display rec_seq,acc_id & security_id)
    (rec_seq=2 display party_name and party_addr_line1)

    1 10001 1
    1 10002 2
    2 10002 b ab
    1 10002 3
    2 10002 b bc
    2 10002 jk lm

    Please help me out..

    Thanks in advance!!

    Reply
  • thank you very much

    Reply
  • thank you very much, you save me live.

    Reply
  • Nikhilesh Patidar
    February 24, 2012 1:47 pm

    –Select name FROM sys.databases order by name

    –Create DataBase And Drop Database
    declare @i int
    declare @sSQL nvarchar(50)
    –Create DataBase
    set @i=1
    while (@i<=10)
    begin
    print @i
    set @sSQL='Create database abc'+ cast(@i as nvarchar(50))
    EXEC(@sSQL)
    set @i=@i+1
    end
    –Drop DataBase
    set @i=1
    while (@i<=10)
    begin
    print @i
    set @sSQL='Drop database abc'+ cast(@i as nvarchar(50))
    EXEC(@sSQL)
    set @i=@i+1
    end

    Reply
  • i want program in for..loop ..give some example

    Reply
  • I have one query below is the details,

    Tab:

    col1 Key col2 startdate enddate
    1 10 100 20111215 29991231
    1 10 100 20111220 20111225
    1 10 100 20111215 29991231
    1 10 100 20111215 29991231

    I want output as

    col1 Key col2 startdate enddate
    1 10 100 20111215 20111219
    1 10 100 20111220 20111225
    1 10 100 20111215 20111224
    1 10 100 20111225 29991231

    i think need to write a loop which will update the endate properly.

    Please help…

    Reply
  • Hi everyone,
    I have a table which holds data regarding ‘TicketNo’,its ‘Mkrdt’,’Loginid’,’Mkrid’ etc.. I want to list the ‘Startdate’ of each ticket , coz each ticket processing is done many days , and i want to also list ‘Days Taken’ for processing that ticket no.. I had a solution, but i get parcial results..I WANT TO LIST STATDATE N DATSTAKEN IN FRONT OF EACH ENTRY OF A PERTICULAR TICKET. .. I had a query related to this as ..

    SELECT a.ticketno, a.mkrdt
    FROM Tbl_Newtagg1 a, (SELECT TicketNo, min(mkrdt) AS StartDate FROM Tbl_Newtagg1 GROUP BY TicketNo) b
    WHERE a.TicketNo = b.TicketNo AND a.mkrdt =b.startdate

    –but it lists every ticket n its startdate only once.. plz help.. Thanks in advance

    Reply
  • try using UNION function with above query and other query which will give only start date

    Reply
  • Michael n Madonna
    May 11, 2012 11:24 pm

    you just copy all your sample in msdn you – oink oink.

    Reply
  • Great Examples!
    I would love to share this on my blog.
    Please let me know if you would like to keep this posting content within your blog only.

    Thank you

    Enma

    Reply
  • Creatives Sys
    June 15, 2012 5:14 pm

    Nice way

    Reply
  • Genial !! muchas gracias.

    Reply
  • The best way is use a cursor:

    example To Delete All Products

    and if 1 product record has dependencies

    For each Product record, there is a record also in Product Inventory table

    DECLARE
    cursor c1 is
    SELECT
    ID AS i_PRODUCTID,
    FROM PRODUCT
    ORDER BY ID ASC;

    BEGIN
    FOR DELETEPRODUCTRECORD in c1
    LOOP
    DELETE FROM PRODUCT_INVENTORY WHERE PRODUCTID = DELETEPRODUCTRECORD .i_PRODUCTID;

    DELETE FROM PRODUCT WHERE ID = DELETEPRODUCTRECORD .i_PRODUCTID;

    END LOOP;

    END;

    ==

    In terms of performance and simplicity, this is the best method i have seen

    Reply
    • B. Shunmuga Sundari
      October 18, 2012 12:39 pm

      hi i am shunmuga sundari,
      i have one doubt that ” is it possible execute a ‘insert into’ query into the while or for loop?”

      Reply
  • how to use vb function to sql query function

    ex:
    function emp()

    str=”select * from employ”
    set rs=conn.execute(str)

    do while rs.eof=false

    for i= 1 to 6

    if sex=’m’ then

    update status=’male’ where empcode=” & rs!emocode & ”

    else

    update status=’female’ where empcode=” & rs!emocode & ”

    next

    loop

    end function

    Reply
  • Can we replace column values of a table with a series of values using While statement?

    Reply

Leave a Reply