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
SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
62 Comments. Leave new
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.