SQL WHILE LOOP Examples and Alternatives

By: Daniel Calbimonte | Updated: 2022-03-04 | Comments | Related: > TSQL

Problem

Sometimes there is a need to loop through records and process a record at a time in a Transact-SQL script or stored procedure in Microsoft SQL Server. It may not be the most efficient approach, but it may be the only option. In this tutorial, we look at to create a WHILE loop along with looking at CTEs and cursors.

Solution

In this tutorial, we will look at examples of a WHILE loop in T-SQL and discuss alternatives like a CTE and cursor.

SQL WHILE Loop Syntax

The syntax is like this:

WHILE CONDITION
BEGIN
   CODE 
   BREAK --Optional 
   CONTINUE --Optional 
END

WHILE Loop Example

In this simple example, we will create a table named emails with an id and email columns and add 100 fake ids and emails by using a WHILE loop.

First, create the table in SQL Server Management Studio (SSMS):

CREATE TABLE #email
(
  id smallint,
  email varchar(50)
)

Next, add this code to generate ids from 1 to 100 and random emails:

DECLARE @count smallint = 0

WHILE @count<100
BEGIN
  INSERT INTO #email VALUES(@count,CONCAT('user',FLOOR(RAND()*1000),'@outlook.com'))
  SET @count=@count+1
END

The code inserts values from 1 to 100 for the id and creates emails like [email protected], [email protected].

Let’s take a look at the data inserted.

SELECT *
FROM #email

As you can see, the WHILE can be useful to iterate data.

This may not be the most efficient method do the above, but it does show an example of using WHILE. In all cases SQL Server performs best with set based operations where everything happens at once instead of having to process row by row. But as mentioned, for something there may not be an alternative.

CTE vs SQL Server WHILE Loop

WHILE is very simple to understand, but it is not so efficient. Database developers usually try to solve the previous problem using CTEs. CTE stands for Common Table Expressions which is a temporary named result set. CTEs are very powerful because they can refer to themselves (recursive common table expressions).

Let’s take a look at an example. We will generate an email table with the id and dummy emails like we did with WHILE in the previous example.

WITH numbergenerator (id, email) AS 
(
   SELECT 1 AS id, CONCAT('user',floor(1000*RAND(CHECKSUM(NEWID()))),'@outlook.com') as email
   UNION ALL
   SELECT ng.id + 1  AS id, CONCAT('user',floor(1000*RAND(CHECKSUM(NEWID()))),'@outlook.com')
   FROM numbergenerator ng
   WHERE ng.id < 100
)
SELECT  *
INTO #numbergenerator
FROM numbergenerator ng;
GO

The code creates a CTE with ids from 1 to 100 and also creates dummy emails. We then use SELECT INTO to create a table named #numbergenerator.

If we select data from the table, we will see that we have 100 rows with ids and emails:

SELECT * FROM #numbergenerator

WHILE Loop Statement with Break and Continue

The BREAK statement and CONTINUE statement are options to break the WHILE loop if there is a condition met and CONTINUE allows continuing with the iteration. They usually are used with IF logic.

For this example, I will create a temporary table named #SalesOrderDetail which is data from the Sales.SalesOrderDetail table from the AdventureWorks database.

SELECT * INTO #SalesOrderDetail
FROM [Sales].[SalesOrderDetail]

--If we check the SUM of the OrderQty we will see that it is 274914.
SELECT SUM([OrderQty])
FROM #SalesOrderDetail

Now we will use the following T-SQL code to update the OrderQty while the SUM of OrderQty is less than 300,000. For each row we will add 1000 to the OrderQty. If the maximum value of OrderQty for any record is greater than 3000, we will BREAK the loop, otherwise we will CONTINUE.

WHILE (SELECT SUM([OrderQty]) FROM #SalesOrderDetail ) < 300000
BEGIN 
   UPDATE #SalesOrderDetail SET [OrderQty] = [OrderQty] + 1000
 
   IF (SELECT MAX(OrderQty) FROM #SalesOrderDetail) > 3000
      BREAK
   ELSE
      CONTINUE
END

WHILE vs Cursors vs Concatenation Queries

Many DBAs recommend avoiding cursors. Cursors are considered a bad practice and you should avoid them if possible. The main problem is the poor performance that it has. Always make sure to close and deallocate your cursor.

The WHILE loop according to SQL Server Loop through Table Rows without Cursor article states that a WHILE is faster than a cursor and uses less locks and use less TEMPDB resources. However, WHILE loops are still slow and have a performance impact. If it is a nested loop, it will be even worse. In general, we should try to use set operations for the best performance. It is not easy, but it is usually the best way. Cursors and WHILE loops must be used carefully and only when absolutely needed.

We have new examples to generate backups of multiple databases using a cursor, a WHILE loop, and a simple query to generate backup statements.

For more information about this code, please refer to this article: Simple script to backup all SQL Server databases

We will create two small databases and then create backups for them.

CREATE DATABASE TEST1
GO

CREATE DATABASE TEST2
GO

Using a Cursor

The code uses a cursor will create backups in the c:\backup folder. Make sure to have that folder created and run the code.

DECLARE @name NVARCHAR(256) -- database name 
DECLARE @path NVARCHAR(512) -- path for backup files 
DECLARE @fileName NVARCHAR(512) -- filename for backup 
DECLARE @fileDate NVARCHAR(40) -- used for file name 
 
-- 1 - Initialize Variables
SET @path = 'C:\Backup\' 
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  

-- 2 - Declare Cursor
DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name like 'test%' -- just get our test databases
 
-- 3 - Open the Cursor
OPEN db_cursor
 
---- 4 - Fetch record from the cursor
FETCH NEXT FROM db_cursor INTO @name   

---- 5 - Check status for the cursor
WHILE @@FETCH_STATUS = 0   BEGIN  
   
   -- 6 - Begin the custom business logic
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
   BACKUP DATABASE @name TO DISK = @fileName 
 
   -- 7 - Fetch the next record from the cursor
   FETCH NEXT FROM db_cursor INTO @name 
END  

---- 8 - Close the cursor
CLOSE db_cursor   

---- 9 - Deallocate the cursor
DEALLOCATE db_cursor

This should create 2 backups in the backup folder as follows.

Using a WHILE Loop

The next example will do backups of the 2 databases. We will create a temp table with the list of our 2 databases first.

SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row, name
INTO #listdatabases
FROM sys.databases 
WHERE name LIKE 'test%'

The #listdatabase temporary table contains all the databases in a numbered list of databases that we want to backup.

SELECT *
FROM #listdatabases

Next, we will use WHILE to create backups for each database using the temporary table we just created.

DECLARE @name NVARCHAR(256) -- database name 
DECLARE @path NVARCHAR(512) = 'C:\Backup\' -- path for backup files 
DECLARE @fileName NVARCHAR(512) -- filename for backup 
DECLARE @fileDate NVARCHAR(40) -- used for file name 
DECLARE @count int = 1

SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)  
DECLARE @limit int = (SELECT count(database_id) FROM sys.databases WHERE name LIKE 'test%');
 
--select row from databases
WHILE @count <= @limit
BEGIN
   SET @name = (SELECT name FROM #listdatabases WHERE row=@count)
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
   BACKUP DATABASE @name TO DISK = @fileName 
     
   SET @count = @count + 1
END

Using a Query to Generate Commands

Finally, we will use a simple query to generate T-SQL code to back up the databases in the following example:

DECLARE @path NVARCHAR(512) = 'C:\Backup\' -- path for backup files 
DECLARE @fileDate NVARCHAR(40) = CONVERT(NVARCHAR(20),GETDATE(),112) -- used for file name 
 
SELECT CONCAT('BACKUP DATABASE',SPACE(1),name, SPACE(1),'TO DISK=''',@path,name,'_' , @fileDate , '.BAK''')
FROM sys.databases 
WHERE name LIKE 'test%'

The code will generate the following SQL statements:

You then need to copy and paste the results into a query window and generate the backups using the created backup commands. The code could also be added to a script file. The backups will still run one at a time like the WHILE and cursor examples.

Next Steps

For more information refer to the following links:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-03-04

Learn the SQL WHILE LOOP with Sample Code