Problem
Sometimes there is a need to loop through records and process a record at a time in a Transact-SQL script, queries 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 how 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 for a WHILE loop in a SQL query is like this:
WHILE CONDITION
BEGIN
CODE
BREAK --Optional
CONTINUE --Optional
ENDWHILE 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 WHILE loop in a SQL query 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
ENDThe code inserts values from 1 to 100 for the id and creates emails like user34@outlook.com, user345@outlook.com.
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;
GOThe 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 #numbergeneratorWHILE 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 #SalesOrderDetailNow 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
ENDWHILE 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
GOUsing 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_cursorThis 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
ENDUsing 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:
