Learn the SQL WHILE LOOP with Sample Code
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.
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 @[email protected]+1 END
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 [email protected]) 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.
For more information refer to the following links:
- SQL Server Cursor Example
- Different Ways to Write a Cursor in SQL Server
- Avoiding WHILE 1 = 1 loops in SQL Server
- SQL Server T-SQL CASE Statement Examples
- SQL Server CASE Statement Example
- SQL Server SELECT Statement Examples
- SQL FOR Loop Alternatives in SQL Server
About the author
View all my tips
Article Last Updated: 2022-03-04