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';
How BREAK and CONTINUE Work in a WHILE Loop
Here is the simple rule I keep in mind. BREAK exits the innermost WHILE loop right away, and execution continues with the first statement after the END of that loop. CONTINUE skips the rest of the statements in the current pass and jumps back to the WHILE condition. In the first example above, CONTINUE sits at the very end of the loop, so it does not change anything. The loop stops only because BREAK runs once @Flag is greater than 5.
Here is a small example of a WHILE Loop with BREAK and CONTINUE where each keyword does real work. It prints only the odd numbers and stops after 7.
DECLARE @i INT = 0;
WHILE @i < 10
BEGIN
SET @i = @i + 1;
IF @i % 2 = 0 CONTINUE;
IF @i > 7 BREAK;
PRINT @i;
ENDOne caution before you use a loop in real work. Always make sure the loop variable changes on every pass, or the loop will run forever. Loops are great for admin tasks, such as working through a list of databases or deleting old rows in small batches. However, when you need to change many rows at once, a single set-based UPDATE is almost always faster than updating one row at a time inside a loop.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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
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.
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,,
I try your explanation, and query running well, see detail
forgive me, I forgot to say
thanks a lot to Mr SqlAuthority
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.