SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE

WHILE statement sets a condition for the repeated execution of an SQL statement or statement block. Following is very simple example of WHILE Loop with BREAK and CONTINUE.
USE AdventureWorks;
GO
DECLARE @Flag INT
SET
@Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT
@Flag
SET @Flag = @Flag + 1
END
IF
(@Flag > 5)
BREAK
ELSE
CONTINUE
END

WHILE loop can use SELECT queries as well. You can find following example of BOL very useful.
USE AdventureWorks;
GO
WHILE (
SELECT AVG(ListPrice)
FROM Production.Product) < $300
BEGIN
UPDATE
Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice)
FROM Production.Product
IF (
SELECT MAX(ListPrice)
FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT
'Too much for the market to bear';

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – WHILE

About these ads

62 thoughts on “SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE

  1. that was awesome. however i need some clarification via email.
    Q. suppose I have a set of names in form of rows and each name has details corresponding to it below it in the form of rows. e.g

    john smith
    42vfgg
    123wdcfdf
    2qefg
    kelly simpson
    fwf3t
    fr3fv
    f5re
    Obama Clinton
    werf343v
    454fvfg
    4rgr
    jehova shalom
    3423vf
    534tr
    5df
    54
    alabama nelson
    45yb
    4544tgf
    3463fg
    t45

    now suppose we want to delete some names we dont need together with there details, how do we go about the syntax? eg delete kelly simpson with his details and jehova shalom and his details

    thanks

    Like

  2. Is there a time out period during which the value in the while condition needs to be returned? I have a job that does a check as follows and purges old data:

    while exists(select top 1 * from Records (nolock) where filetimeStamp < @d1)
    begin
    set rowcount 10000
    delete from Records where filetimeStamp < @d1
    end

    The query ‘select top 1 * from Records (nolock) where filetimeStamp < @d1′ takes 8 mins to return a value, as the table has around 322 millon rows in it. No indexes on the table. The job is not deleting data properly, and I was wondering if the query in the while condition is timing out causing the issue.

    Like

  3. I have a table with associate names. Another table with one field which is associate dealers. There are no common fields possible between the 2 tables to do any kind of join.
    i want to create a 3rd table with 2 fields, where each associate name in the 1st table is inserted with every dealer in the second table. How do I do this? Thanks

    Like

  4. thank u sir. but i have a problem in sql query and the problem is , i want to select 2 alternative rows of all the table .
    e.g if account number are lioke 1,2,3,4,5,6,7,8,9,10
    i want to select 1,2,5,6,9,10 like that
    but i cant’t do that . please help me as soon as possible .i am a fresher i dont have so much knowldge in sql server but i keen to know, can you help me sir . Please give me a book name throw which i can got som knowldge.
    thanking you,
    Bishnu.

    Like

  5. Hello,

    Please help me ,
    I m not a DBA but i wanna to create log table
    where i have to compare two tables and loop through the column names in two tables…

    to find column name i used

    SELECT SC.NAME AS “Column Name”
    FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
    WHERE SO.xtype = ‘U’
    and SO.NAME =’tblnew’
    ORDER BY SO.[name], SC.colid

    and for Count

    SELECT Count(SC.NAME) AS “Column count”
    FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
    WHERE SO.xtype = ‘U’
    and SO.NAME =’tblnew’

    this new table i have to compare with the old how to do this…

    Like

  6. Hi,
    I have a query like given…Is there any way to give the RoomName dynamically. I have several roomnames in a table. So its not possible to write case for all of them like i have done here. Pls help ASAP. Thnx in advance.

    query
    ——–

    SELECT ItemCode,Sum(Estimate) as projecttotal,
    SUM(CASE WHEN RoomName =’Room1′ THEN Estimate ELSE 0 END ) AS [Room1],
    SUM(case when RoomName=’Room2′ then Estimate else 0 end) as [Room2]
    FROM ReportDataTable
    GROUP BY ItemCode

    Like

  7. @khaled

    This is a small example…

    Declare @Var1 int
    Declare @Var2 int
    Set @Var1 = 1

    While @Var1 < = 10
    Begin
    Print 'This is outer loop Value is :'+Convert (varchar, @Var1)
    Set @Var2 = 1
    While @var2 <= 10
    Begin
    Print ' This is Inner Loop Value is:'+Convert (varchar, @Var2)
    Set @Var2 = @var2+1
    End
    Set @Var1 = @var1+1
    End

    ~IM

    Like

  8. @khaled

    Declare @Var1 int
    Declare @Var2 int
    Set @Var1 = 1

    While @Var1 < = 10
    Begin
    Print 'This is outer loop Value is :'+Convert (varchar, @Var1)
    Set @Var2 = 1
    While @var2 <= 10
    Begin
    Print ' This is Inner Loop Value is:'+Convert (varchar, @Var2)
    Set @Var2 = @var2+1
    End
    Set @Var1 = @var1+1
    End

    Like

  9. Heres a proc I made using your sample:

    CREATE PROCEDURE dbo.prcGetStoredProcedureCode
    @strStoredProcedureName VARCHAR(100) = ‘dbo.sp_helptext’
    AS
    BEGIN
    /*
    Samples:
    EXEC dbo.prcGetStoredProcedureCode
    EXEC dbo.prcGetStoredProcedureCode ‘dbo.prcRebatesExportFormat800′
    */
    DECLARE @tblSQL TABLE (id int IDENTITY(1,1), [Text] varchar(max))

    INSERT INTO @tblSQL
    EXEC sp_helptext @objname = @strStoredProcedureName

    DECLARE @strSQL VARCHAR(MAX)
    DECLARE @intFlag int
    DECLARE @intCount int
    SET @intCount = (SELECT COUNT(*) FROM @tblSQL)
    SET @intFlag = 1
    SET @strSQL = ”
    WHILE (@intFlag @intCount)
    BREAK
    ELSE
    CONTINUE
    END

    –SELECT * FROM @tblSQL

    SELECT @strSQL AS strSQL

    END

    Like

  10. Let me try this again:

    CREATE PROCEDURE dbo.prcGetStoredProcedureCode
    @strStoredProcedureName VARCHAR(100) = ‘dbo.sp_helptext’
    AS
    BEGIN
    /*
    EXEC dbo.prcGetStoredProcedureCode
    EXEC dbo.prcGetStoredProcedureCode ‘dbo.prcRebatesExportFormat800′
    */
    DECLARE @tblSQL TABLE (id int IDENTITY(1,1), [Text] varchar(max))

    INSERT INTO @tblSQL
    EXEC sp_helptext @objname = @strStoredProcedureName

    DECLARE @strSQL VARCHAR(MAX)
    DECLARE @intFlag int
    DECLARE @intCount int
    SET @intCount = (SELECT COUNT(*) FROM @tblSQL)
    SET @intFlag = 1
    SET @strSQL = ”
    WHILE (@intFlag < (@intCount + 1))
    BEGIN
    SET @strSQL = @strSQL + (SELECT [Text] FROM @tblSQL WHERE id = @intFlag)
    SET @intFlag = @intFlag + 1

    IF(@intFlag > @intCount)
    BREAK
    ELSE
    CONTINUE
    END

    SELECT @strSQL AS strSQL
    END

    Like

  11. Dear Sir,

    Can u tell me how to insert multiple column records into a coulmn with comma separator.

    I hav two column called col1,col2
    Col1, col2
    1 1
    1 2
    1 3
    2 a
    2 b
    2 c
    3 p
    3 q
    3 r

    I want the result should be like as follows :

    col1 col2
    1 1,2,3
    2 a,b,c
    3 p,q,r

    How do I get the same can u please help me regarding this ?

    Like

    • [quote]
      Dear Sir,

      Can u tell me how to insert multiple column records into a coulmn with comma separator.

      I hav two column called col1,col2
      Col1, col2
      1 1
      1 2
      1 3
      2 a
      2 b
      2 c
      3 p
      3 q
      3 r

      I want the result should be like as follows :

      col1 col2
      1 1,2,3
      2 a,b,c
      3 p,q,r

      How do I get the same can u please help me regarding this ?[/quote]

      In Reply To This

      Set Xact_Abort On
      Select Col1 + ‘,’, Col2 As Col3 From TableName With (NoLock)

      Like

  12. Hi I have a problem while fetching value’s in SQL server 2005.

    I have a table called Schedule where i have these column and value upto 7 day.
    Empid Day Mst Met Est Eet Slt
    00012 1 1:00AM 2:00AM 3:00PM 4:00PM 15
    2
    3

    I need a solution for fetching the value,
    1-If slt (slot) is 10 than in betwee Mst time and Met Time i need to fetch these value as like ?

    1:00
    1:15
    1:30
    1:45
    2:00

    same as in Est and Eet time according to slot
    2-If i send the day parametre (1,2,3) than for particular day it ll happen and the date for particular day should come in a temp column infront of that?

    Can any one give the solution of this problem,i need.

    Like

  13. Hi,
    I have text values in one column and they are seperated by ‘|’ delimiter.

    eg:-
    234|33|432|44
    result is coming as:-234

    Expected result is 234
    33
    432
    44

    When I split them I am still getting only the first value and not all values seperated.

    How can I get all the seperated values?

    Like

  14. Dear Sir,

    I have a 2 columns having records like:
    Col1 Col2
    Fruits Apple
    Fruits Mango
    Fruits Orange
    Sport Criket
    Sport Football
    Dress Jeans

    Now i want to fetch result like :
    Fruits
    Apple
    Mango
    Orange
    Sport
    Criket
    Football
    Dress
    Jeans

    How can i fetch this result . Please help

    Like

  15. dear sir,

    i have this table:
    LOAN BAL INT PI CRTERM STATE
    1 10000 4.5 150.28 0 TN
    2 20000 4.75 168.89 0 MS
    3 30000 5 212.56 0 TN
    4 40000 5.25 243.26 0 MS
    5 50000 5.5 305.26 0 TN
    6 60000 6 345.2 0 AL

    i need to count how many times a certain state shows up plus each states balance added up.

    please help

    Like

  16. I have table1 in that i have 2 column like
    PERSON_NO Current_IND
    101 1
    101 -1
    101 0
    102 -1
    103 0
    103 -1

    Now in result i want to identified person_no which has current_ind= -1
    Condition 1: that same person_no should not have current_ind=1…

    From above table my result will be :
    Person_no Current_ind
    102 -1
    103 -1

    (Note : 101 will not come in result as its having current_ind =1 )

    Like

    • @bhumika shah

      Here’s one way:

      WITH
      table1(PERSON_NO, Current_IND)
      AS
      (
      SELECT 101, 1 UNION ALL
      SELECT 101, -1 UNION ALL
      SELECT 101, 0 UNION ALL
      SELECT 102, -1 UNION ALL
      SELECT 103, 0 UNION ALL
      SELECT 103, -1
      )
      SELECT
      PERSON_NO,
      MAX(Current_IND) Current_IND
      FROM
      table1
      WHERE
      Current_IND IN(-1, 1)
      GROUP BY
      PERSON_NO
      HAVING
      MAX(Current_IND) = -1;

      Like

  17. Hi,

    Thanks so much for this useful article. I am a newbie to MSSQL and I have a problem which I can’t solve yet.

    There is a table which records Internet users (accountid) and their input/output bytes for a session. The columns of interest are (accountid, inbytes, outbytes) respectively. There are many users and a user may have more than 1 session (1 row in a table) for a day. Here is an example of that table:

    day accountid inbytes(MB) outbytes(MB)
    1/1/2010 32 80 3
    1/1/2010 50 5 20
    1/1/2010 32 1 15
    2/1/2010 11 0 0
    3/1/2010 50 100 24
    .
    .
    .
    29/1/2010 11 54 12

    I need to calculate a user’s total data usage (inbytes+outbytes) for a given period (user will specify from which day to till which day). I then need to output it as a table on a webpage, like this:

    AccountID Totalusage(MB)
    11 2000
    32 586

    I can figure out the table drawing part I think. Problem is with the calculation of each user’s usage. I know it’s not so difficult but I am very new to MSSQL and I don’t have very good books.

    Thanks so much for the time and help.

    Like

    • @Phyo

      Select AccountID
      ,SUM(ISNULL(InBytes,0), ISNULL(OutBytes,0) TotalUsage
      From MyTable
      Where Day Between @StartDate AND @EndDate
      Group BY AccountID

      Here @StartDate and @EndDate are parameters that user will provide.

      1. You can put this in a stored procedure that accepts StartDate and EndDate as a parameter.

      2. Based on the format you are using Day, you can use convert function and style to compare dates.
      Ex:
      Select AccountID
      ,SUM(ISNULL(InBytes,0), ISNULL(OutBytes,0) TotalUsage
      From MyTable
      Where Convert (varchar,Day,102) Between Convert (Varchar,@StartDate,102) AND Convert (Varchar,@EndDate, 102)
      Group BY AccountID

      ~Peace

      Like

  18. @Phyo

    Small Correction in SQL Statement, it should be Plus but not a comma,

    Corrected SQL :
    ————————————-

    Select AccountID
    ,SUM(ISNULL(InBytes,0)+ ISNULL(OutBytes,0) TotalUsage
    From MyTable
    Where Day Between @StartDate AND @EndDate
    Group BY AccountID

    ~Peace.

    Like

  19. Thanks so much Imran. It works like a charm!! So, GROUP BY is the function I was looking for. I was modifying an existing query file and when I first tried, MSSQL gave me an error saying that I need to put all the data fields in the select statement in GROUP BY clause. Now, I tried like you said and it works wonderfully. Thanks once again. Have a nice day.

    Like

  20. Hi Sir,

    How to get fast execution of this below code

    ALTER PROC [dbo].[usp_PopulateNumber4] @Start int = NULL, @End int = NULL AS

    DECLARE @Digit TABLE (digit int NOT NULL);

    SET NOCOUNT ON;
    DECLARE @i int = @Start;
    BEGIN TRAN
    WHILE @i <= @End
    BEGIN
    INSERT Number (Num) SELECT @I
    SET @i += 1;
    END
    COMMIT

    Like

  21. My table (Bill) is as following

    Doc_no Date ItemName Qty
    A1001 27/09/10 Pen 5

    Select * From Bill where Doc_No = ‘A1001′

    I want to repeat above line number of Qty Times (5 Times) with qty 1.

    Plz help, thnx in advance.

    Like

  22. DECLARE @Digit TABLE (digit int NOT NULL);

    SET NOCOUNT ON;
    DECLARE @i int = @Start;
    BEGIN TRAN
    WHILE @i <= @End
    BEGIN
    INSERT Number (Num) SELECT @I
    SET @i += 1;
    END
    COMMIT

    Like

  23. Declare @Var1 int
    Declare @Var2 int
    Set @Var1 = 1

    While @Var1 < = 10
    Begin
    Print 'This is outer loop Value is :'+Convert (varchar, @Var1)
    Set @Var2 = 1
    While @var2 <= 10
    Begin
    Print ' This is Inner Loop Value is:'+Convert (varchar, @Var2)
    Set @Var2 = @var2+1
    End
    Set @Var1 = @var1+1
    End

    Like

  24. Hi Pinal Dave and Forum,

    I was hoping if you guys can point me to the right direction.

    I do have the following table scenario

    Columns: Year, Period, MonthToDate, YearToDate

    Row1: 2010, 0, 100, 100
    Row2: 2010, 3, 200, 300
    Row3: 2010, 9, 300, 600

    I was hoping to create a While script that will insert the missing periods (up to period 12), and then place 0 for the MonthToDate, while also making sure the YearToDate values are correct.

    As such, I was hoping to get the following result set:

    Columns: Year, Period, MonthToDate, YearToDate

    Row1: 2010, 0, 100, 100
    Row2: 2010, 1, 0, 100
    Row3: 2010, 2, 0, 100
    Row4: 2010, 3, 200, 300
    Row5: 2010, 4, 0, 300
    Row6: 2010, 5, 0, 300
    Row7: 2010, 6, 0, 300
    Row8: 2010, 7, 0, 300
    Row9: 2010, 8, 0, 300
    Row10: 2010, 9, 300, 600
    Row11: 2010, 10, 0, 600
    Row12: 2010, 11, 0, 600
    Row13: 2010, 12, 0, 600

    It looks like I would also need some grouping on the script.

    Any ideas you have would be greatly appreciated.

    Thank you for your time.

    Regards,

    Eric

    Like

  25. Hi Pinal and Forum,

    Your blog is very helpful. But i am stuck with something different. I have table having following columns.

    C_ID C_ Rank NewColumn(Cycle) Date
    42 A 1 October 14, 2010
    42 B 1 October 26, 2010
    42 A 2 February 16, 2011

    43 A 1 December 17, 2010

    44 A 1 July 28, 2010
    44 B 1 August 10, 2010
    44 A 2 January 11, 2011
    44 B 2 January 28, 2011

    45 A 1 July 30, 2010
    45 B 1 August 9, 2010
    45 B 1 September 24, 2010
    45 A 2 April 5, 2011
    45 B 2 April 26, 2011

    Actually table has three columns(C_ID, C_rank, Date). I want to generate one more column called Cycle in such a way that for each C_ID, it should generate the number start from one and increment the number from next C_rank ‘A’ (a shown above).
    I tried using row_number, but no luck.
    May be some loop option till next C_Rank ‘A’ works.
    This is urgent.
    Any help is appreciated.

    Thanks.

    Like

  26. @Punia.

    I am sure there is a better way of doing this, but below script is the quiest way….

    /*
    THIS IS SETUP TABLE, WITH SOME SAMPLE DATA
    */
    if exists (select 1 from tempdb.sys.objects where name like ‘#Temp_Table%’)
    drop table #Temp_Table

    create table #Temp_Table
    (
    C_ID int
    , C_Rank char(1)
    , Date datetime
    , NewColumn int
    )

    insert into #Temp_Table
    (
    C_ID
    , C_Rank
    , Date
    )
    select 42, ‘A’, ’10/14/2010′
    union all
    select 42, ‘B’, ’10/26/2010′
    union all
    select 42, ‘B’, ’10/14/2010′
    union all
    select 42, ‘C’, ’10/26/2010′
    union all
    select 42, ‘A’,’02/16/2011′
    union all
    select 43, ‘A’, ’12/17/2010′
    union all
    select 44, ‘A’, ’07/28/2010′
    union all
    select 44, ‘B’, ’08/10/2010′
    union all
    select 44, ‘A’, ’01/11/2011′
    union all
    select 44, ‘B’, ’01/28/2011′
    union all
    select 44, ‘C’, ’10/14/2010′
    union all
    select 44, ‘D’, ’10/26/2010′

    Select ‘Original Data’ Comment
    ,*
    from #Temp_Table

    /*
    This would be Actual Script to get the New ID based on information you provided
    */
    Declare @Count int
    ,@C_ID int
    ,@C_Rank char(1)
    ,@total_Count int
    ,@Count_Partition int
    ,@Previous_ID int

    Declare @Table Table (ID int IDENTITY(1,1), C_ID int, C_Rank char(1), Date datetime, NewColumn int )

    Set @Count = 1
    Set @Count_Partition = 0

    insert into @Table
    Select *
    from #Temp_Table

    Select @total_Count = ISNULL(MAX(ID),0)
    from @Table

    While @Count < = @total_Count
    Begin
    Select @C_ID = C_ID
    ,@C_Rank = C_Rank
    From @Table
    Where ID = @Count

    If @Count = 1
    Set @Previous_ID = @C_ID

    If @Previous_ID != @C_ID
    Set @Count_Partition = 1

    Else If @C_Rank = 'A'
    Set @Count_Partition = @Count_Partition + 1

    update @Table
    Set NewColumn = @Count_Partition
    Where ID = @Count

    Set @Previous_ID = @C_ID
    Set @Count = @Count + 1
    End

    Select C_ID
    , C_Rank
    , [Date]
    , NewColumn
    from @Table

    –Drop table #Temp_Table

    ~ IM.

    Like

    • @Punia,

      One Important assumption I made here was, First C_Rank Value for any C_ID would be ‘A’. If not, you have to modify script accordingly.

      ~IM.

      Like

  27. Thanks Imran,
    This solves my problem.
    I have one more question. I have about 14,000 rows. So when i use your logic, it gives the correct result, but performance degrades (takes about 4 minutes to execute). Is there is any solution to decrease the execution time.

    Thanks again for your help.
    Punia

    Like

  28. @Punia,

    See, I knew this would kill performance. Use this for now. In couple of days I will update another version which I can assure you will be much faster than this.

    But, I wonder, 14,000 records execution time is 4 minutes… That is worst than I expected.

    What is your Server Specs ?

    ~ IM.

    Like

  29. Hi Dave,
    My query is , im having table sale

    Sale{Cleant_name,Product}
    eg:
    Client_name | Product
    a | 123
    a | 321
    b | 123
    output must be in this pattern:
    Client_name | product1 | product2
    a | 123 | 321
    b | 123

    Thanks in advance
    waiting for reply

    Like

  30. I have problem in deleting records from the table.
    Below is the requirement.

    I have table UserLogins(UserId,Logindate)
    Data Like
    UserId Logindate
    111 01/12/2012
    111 02/12/2012
    111 03/12/2012
    111 04/12/2012
    222 01/12/2012
    222 02/12/2012
    222 03/12/2012
    222 04/12/2012
    333 01/12/2012
    333 02/12/2012
    333 03/12/2012
    333 04/12/2012

    I want to delete the records from this table and I want retain only last 3 recent records for each userid.

    Any help on this Appreciated.

    Like

  31. sir, i want to create the result as follows..
    the id and date is here in tablw.i want to collect the sum of datediff in as order as employee id…in table,,date in punch in and punch out…iwant to diff alternate table…then their sum …which is considered as slno.. ie,
    slno 1
    employeeId 1
    time 6(in hr)
    can u plz help me sir,,

    Like

  32. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  33. I am trying to make use of the while loops for the following application:
    My data is in the form (data source Excel):
    Name, Group, FY13Q1, FY13Q2
    Name1, Software, Project1, Project2
    Name2, Hardware, Project2, Project3
    ….
    each additional row lists a new employee, the group they belong to and which project they’re working on in a given quarter. For a fixed file format, I created an SQL script that flattens the data using the following:
    SELECT [Sheet2$].[Name] AS [Name],
    [Sheet2$].[Group] AS [Group],
    ‘FY13Q2′ AS Quarter,
    [Sheet2$].[FY13Q2] AS Project
    FROM [Sheet2$]
    UNION ALL
    SELECT [Sheet2$].[Name] AS [Name],
    [Sheet2$].[Group] AS [Group],
    ‘FY13Q3′ AS Quarter,
    [Sheet2$].[FY13Q3] AS Project
    FROM [Sheet2$]

    This works fine, but I want to be able to handle a variable number of input columns, in which the FY13Q2 etc can vary. I want to check the existance of a column name and if exists, then match it’s name to a predetermined set of possibilities and then automatically use Union All for each column.

    Is this possible at all?
    Of course, the alternative is to edit the script above and customize it to match the input data stream.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s