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)

Solarwinds
, , ,
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

  • hi i have a string like
    HHHHHHsuhhhsusu each character represents a date starting from 28/07/2012 and im trying to select it or create a table
    create table #calender (string as char(5)COLLATE SQL_Latin1_General_CP1_CI_AS, dat datetime)
    declare @counter as int
    set @counter = 3
    while (@counter < 367)
    begin
    –print @counter

    –insert into #calender (string,dat)
    select distinct substring (CCAL_Available_Days
    ,@counter,1), dateadd (day,@counter,'2012-07-28 00:00:00.000') date from dbo.CCALCalend
    where CCAL_Year = '2012'
    set @counter = @counter+1
    –select * from #calender

    end
    drop table #calender
    i am getting the error
    The column "string" cannot be modified because it is either a computed column or is the result of a UNION operator.
    can you please help

    Reply
  • declare @i int
    declare @n int
    set @i=0
    set @n=10
    while(@i<=@n)
    BEGIN
    PRINT 'A'
    set @i=@i+1
    END

    i have doubt when i use the variables i and n as integer type ,A is printed 11
    times
    declare @i varchar(10)
    declare @n varchar(10)
    set @i=0
    set @n=10
    while(@i<=@n)
    BEGIN
    PRINT 'A'
    set @i=@i+1
    END

    when i use the variables i and n as varchar ,A is printed twice can you please tell me the reason y this could be like this

    Reply
    • When you use the Variable as varchar, it will take the ASCII value of 0 and 10 because they are treated as character, ASCII(0) = 48 and ASCII(10)=49, when you do the comparison on character value in SQL, SQL do that on its ASCII value. That is why your loop work for two times i.e. for 48 to 49.

      Reply
  • Hi i have one city table. in that i have to calculate the distance url between those cities. for example i have 5 city names like chennai,hyderabad,kolkuta, delhi, and mumbai. in that i have to calculate distance url like chennai to hyderabad, chennai to kokata, chennai to delhi, chennai to mumbai. i have query for single city but we have many cities in that table. i want to run the query for all cities in single query please help me….

    Reply
  • Hello sir,
    I need help from you
    i have two columns
    let me give example
    masterid attributename attributevalue
    ————- ——————– —————–
    1 mode_code barge
    1 sap_code B
    1 Description g

    here i know how many attribute names are there so i can use cases here to update.

    UPDATE CORE.MasterAttr
    SET AttributeValue = (
    CASE AttributeName
    WHEN ‘DESCRIPTION’ THEN ‘Barge’
    WHEN ‘SAP_MODE’ THEN ’01’
    WHEN ‘MODE_CODE’ THEN ‘B’
    END
    ) WHERE MasterId=1

    but in real scenario i dont know how many attribute name will be available.
    so i need to write storedprocedure .the input values will come from c#.
    suppose for master id 2 i can have 12 attribute names and i have to update attribute vales for those attribute names.
    if we give give masterid 4 and we can get 15 attribute names etc.

    so how can we dynamically loop it and write stored procedure sir.it will really helps me alot.can u please help me on this.

    Reply
  • Arunraja (Rocket)
    February 3, 2013 3:58 pm

    Hi Pinal,
    Hope you are doing good.

    The looping query pasted below works fine in updating ‘hour’ in a date time column using NTILE().

    +++++++++++++++++++
    DECLARE @loop int
    DECLARE @inc int

    SET @inc=1
    WHILE (@inc<=24)

    BEGIN

    ;with up AS
    (
    SELECT salesorderdetailid,ntile(24) OVER (order by productid ) as ntile24
    from sales.salesorderdetail
    )
    UPDATE sales.salesorderdetail
    SET modifieddate=DATEADD(hh,@inc,modifieddate)
    from sales.salesorderdetail
    where salesorderdetailid in
    (
    select salesorderdetailid from up
    where NTILe24 =@inc
    )
    PRINT @inc
    SET @inc=@inc+1
    END

    –select * from sales.salesorderdetail tablesample (1000 rows)

    +++++++++++++++++++

    Is there any simplest way to solve this problem?
    OR
    If the query can be modified without using CTE?

    Kindly suggest how to increase the query performance when handling these situations.

    Thanks,
    Arunraja.

    Reply
  • I have created a view obtaining the RunningTotal joining the query] to itself that uses ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location, Time
    ON a.Row >= b.Row AND a.location = b.location
    SUM(b.Qty) AS RunningTotal

    Here is a sample of the results – Question following sample

    Rownumber Location Time X1 X2 X3 Qty RunningTotal
    1 EAST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
    2 EAST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
    3 EAST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
    4 EAST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
    5 EAST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
    6 EAST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
    7 EAST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
    8 EAST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
    9 EAST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
    10 EAST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
    11 EAST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
    12 EAST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
    13 EAST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
    14 EAST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
    15 EAST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
    16 EAST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
    1 WEST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
    2 WEST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
    3 WEST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
    4 WEST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
    5 WEST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
    6 WEST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
    7 WEST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
    8 WEST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
    9 WEST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
    10 WEST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
    11 WEST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
    12 WEST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
    13 WEST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
    14 WEST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
    15 WEST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
    16 WEST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
    17 WEST 10/23/13 5:45 0.29719 0.1893 0.13493 250 750

    For the calculated results I want need to ignore any record before the LAST RunningTotal of 0 (in the actual table for the same location the RunningTotal could zero out several times over the time span) and get a SumProduct for the X1,X2,X4 values with the final RunningTotal. The query would only use the values in yellow and the results would be :

    Location SumProduct X1 SumProduct X2 SumProduct X3 Total Qty
    EAST 0.26532 0.19359 0.11399 500
    WEST 0.27594 0.19216 0.12097 750

    Can you help me with tthe sql to get these results?

    Reply
  • Sorry the yellow highlighting did not show. Basically for one location I need the values from the record after the LAST time the RunningTotal zeros out, although it may zero out many times before.

    Reply
  • Gourab Mazumder
    January 17, 2014 11:27 pm

    Respected Sir,
    I have error on “php search engine useing sql” . Please tell me how do I create this ?

    Reply
  • table

    id vale
    1 10 20
    2 20 30
    3 30 40
    4 40 50
    5 50 60

    plz this question output

    Reply
  • How to print these values in a single line like ‘1,2,3,4,5’?

    Reply
  • DECLARE @MyCursor CURSOR
    SET @MyCursor = CURSOR FAST_FORWARD
    FOR
    select TOP (100000) * from [10.65.84.4].[CEAT].dbo.Curing_Trend with(NOLOCK) order by Rdatetime asc
    OPEN @Mycursor
    FETCH NEXT FROM @MyCursor
    INTO @Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID,@C_Flag
    WHILE @@FETCH_STATUS = 0
    BEGIN

    INSERT INTO dbo.Curing_Trend (Rdatetime, Barcode, Cavity, IPLH, IPRH, ITLH, ITRH, PTLH, PTRH, SPLH, SPRH, MachineID, ShiftID)
    values(@Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID)

    Update [10.65.84.4].[CEAT].dbo.Curing_Trend SET C_Flag = 0 where Rdatetime = @Rdatetime and Barcode = @Barcode

    FETCH NEXT FROM @MyCursor
    INTO @Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID,@C_Flag
    END
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
    Delete from [10.65.84.4].[CEAT].dbo.Curing_Trend where C_Flag =0
    END TRY

    hi @pinal dave
    this is my Cursor loop to insert update and delete.
    please can you suggest how can i use while loop because cursor takes too much time to execute

    Reply
  • You need to find slow query in the loop. How many rows? and do you have right indexes? Looks like linked server query also involved. How is the query plan?

    Reply
  • this is a 10 minute job activity which transfers 100000 records to another table in another server through the concept of linked server

    Reply
  • Santhosh Raja
    April 18, 2017 2:54 pm

    Main issue is that i dont have indexes

    Reply
  • HI dave Please can you suggest any efficient methods?

    Reply
  • Kushal Makwana
    May 3, 2018 11:31 pm

    Hi Pinal there is a strange requirement if you can provide your inputs and can achieve this in the SQL server. I have to compare the previous rows and find the delta for 100 around columns.So can we make it a procedure which inputs the column name from excel spreadsheet and loop around each and every field to provide the difference with the previous row?

    Reply
  • Kranthi Tiwari
    June 3, 2019 7:03 pm

    Hi. Is sql server 2005 and 2008 support while loop?

    Reply
  • I am having employee table. Now i need to update a table through stored procedure where emp_id should 1000+ and if the emp_id is less than thousand it should be updated

    ALTER PROCEDURE EMPLOYEE
    AS
    BEGIN
    DECLARE @EMP_ID INT
    SELECT @EMP_ID=EMP_ID FROM EMPLOYEE
    UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID+1000
    IF @EMP_ID<1000
    UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID+1000
    ELSE
    UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID

    Reply

Leave a Reply

Menu