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

  • DRY does not need a while loop — it’s SQL. try:

    SELECT @SQL = @SQL +[Text]
    FROM @tblSQL
    WHERE id <= @intCount
    ORDER BY id

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

    Reply
  • krishnapriya
    May 31, 2012 11:12 am

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

    Reply
  • I try your explanation, and query running well, see detail

    Reply
  • forgive me, I forgot to say
    thanks a lot to Mr SqlAuthority

    Reply
  • Varoujan Chakarian
    August 8, 2013 5:17 am

    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.

    Reply

Leave a Reply Cancel reply

Exit mobile version