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 (https://blog.sqlauthority.com) , BOL – WHILE

Previous Post
SQL SERVER – FIX : ERROR : Cannot find template file for new query (C:\Program Files\Microsoft SQL Server\90\Tools\ Binn\VSShell\Common7\ IDE\sqlworkbenchprojectitems\Sql\ SQLFile.sql)
Next Post
SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

Related Posts

62 Comments. Leave new

  • 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?

    Reply
  • Thanks Brian but the method given in the link is not what I require.I am not saving my values in any file.

    Reply
  • Manish Srivastav
    April 5, 2010 9:59 am

    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

    Reply
  • 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

    Reply
  • bhumika shah
    May 11, 2010 5:10 am

    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 )

    Reply
    • Brian Tkatch
      May 11, 2010 5:39 pm

      @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;

      Reply
    • vijaygoud1396
      May 13, 2016 4:46 pm

      Select PERSON_NO,Current_IND
      from table1
      where Current_IND = -1 and
      PERSON_NO not in (select PERSON_NO from #temp where Current_IND =1 and Current_IND !=-1)

      Reply
  • 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.

    Reply
    • Imran Mohammed
      July 16, 2010 6:13 am

      @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

      Reply
  • Imran Mohammed
    July 16, 2010 6:15 am

    @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.

    Reply
  • 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.

    Reply
  • 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

    Reply
    • NSERT Number (Num)
      select @start+number from master..spt_values
      where type=’p’ and number between 0 and @end

      Reply
  • Isn’t this exact example provided on the Microsoft reference website? :-)

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • rafael greca
    March 3, 2011 12:07 am

    Boa tarde,

    como faz qual formulas horario para turno? exemplo

    hora — turno
    6:15 — 1
    9:00 — 1
    15:50 — 2
    23:05 — 3

    Reply
  • 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.

    Reply
  • Imran Mohammed
    May 18, 2011 10:32 am

    @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.

    Reply
    • Imran Mohammed
      May 18, 2011 10:42 am

      @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.

      Reply
  • 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

    Reply
  • Imran Mohammed
    May 19, 2011 3:19 am

    @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.

    Reply
  • 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

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version