Problem
I need to use a loop in Transact (T-SQL), but it seems there’s no FOR loop construct in Microsoft SQL Server. How can I write a loop in my SQL scripts? Can you provide a loop example with a SELECT statement including begin, loop counter and end conditional logic that can be used in stored procedures?
Solution
In programming you often need to iterate over some sort of set, array or list. For example, for each sales order line of an invoice, you want to calculate a possible discount. The key words here are “for each”. For each item of the set, you want to perform some action. In other words, you want “to loop over” the set.
In many programming languages, there’s a FOR loop statement. In pseudo-code, it generally looks like this:
for(int i = 0; i<=length; i++)
{
… do something
}
This piece of code means: for each iteration of the parameter i – which is between 0 and the length parameter – execute the code block between the brackets. If length is 10, the code block will be executed 11 times (for the values 0,1,2,3…,10 of the iterator i). You can execute the same piece of code each time, but typically i itself is used in the code to make it dynamic.
However, SQL code does not have a FOR loop. It does have a couple of alternative SQL commands, which we will discuss in this SQL tutorial.
The WHILE Loop Statement
The most straightforward alternative might be the WHILE loop. After all, many programming languages have a WHILE and DO … WHILE loop constructs besides the FOR loop. The intent of a WHILE loop is a bit different than the one of a FOR loop. The FOR loop will iterate over a fixed number of items, while a WHILE loop will iterate as long as some sort of Boolean condition is true. This means a WHILE loop can run for an infinite time (and this is a real danger if a bug slips into the Boolean condition). But it is perfectly possible to mimic a FOR loop with a WHILE loop. Let’s illustrate with the following example:
DECLARE @i TINYINT = 0;
DECLARE @length TINYINT = 10;
WHILE @i <= @length
BEGIN
PRINT @i;
SET @i += 1;
END
This piece of script will start with @i = 0, print the value of the variable @i, increment that variable with 1 and will continue to do so until @i is equal to 10.
In T-SQL, the contents of the WHILE loop are enclosed within the BEGIN and END keywords. The += operator is shorthand for the following:
SET @i = @i + 1;
Controlling Statements inside the Loop with BREAK and CONTINUE
With the keywords BREAK and CONTINUE the execution of statements can be controlled. When CONTINUE is executed, the WHILE loop skips to the next iteration, ignoring any statements in the loop after CONTINUE. BREAK on the other hands stops the WHILE loop altogether, even if the Boolean condition is still true. Let’s illustrate with an example. Suppose we have the following table with integers:
Now we’re going to iterate over the rows of the table and print out the value of the TestData column. If the value 1 is encountered, we will not print out (using CONTINUE) and if the value 0 is encountered we will stop the loop. This can be accomplished with the following syntax in SQL Server Management Studio (SSMS):
DROP TABLE IF EXISTS #TestTable
SELECT ID = 1, TestData = 4
INTO #TestTable
UNION ALL
SELECT 2, 7
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 4, 3
UNION ALL
SELECT 5, 9
UNION ALL
SELECT 6, 5
UNION ALL
SELECT 7, 0
UNION ALL
SELECT 8, 2
UNION ALL
SELECT 9, 6
UNION ALL
SELECT 10, 8;
DECLARE @i TINYINT = 0;
DECLARE @length TINYINT = 10;
DECLARE @currentValue TINYINT;
WHILE @i <= @length
BEGIN
SELECT @currentValue = TestData
FROM #TestTable
WHERE [ID] = @i;
IF @currentValue = 1
BEGIN
SET @i += 1;
CONTINUE;
END
ELSE IF @currentValue = 0
BREAK;
PRINT @currentValue;
SET @i += 1;
END
When we run the script, we get the following output:
As you can see, we didn’t print out the value 1 in the 3rd iteration and the loop stops in the 7th iteration (end loop) because the value 0 was reached. It didn’t do all 10 iterations.
Cursors
With a cursor you can scroll through a dataset. Cursors have a bad reputation in SQL Server, since they process data row-by-row while typically set-based operations (where you perform some kind of transformation on an entire column instead of a single row) are much faster. However, sometimes you need to implement complex business logic which can only be handled row-by-row or you just need to loop over a small set where there’s no performance impact. Because cursors handle data row-by-row they might be slower, but their performance is linear and thus quite predictable. For example, if it takes 10 seconds to handle 10,000 rows, you can assume it will take about 100 seconds to handle 100,000 rows.
For a good introduction to cursors, please refer to the tip SQL Server Cursor Example. Cursors can be used to implement FOR loop logic as well, just like a T-SQL WHILE loop. Let’s implement the first example where we print out a set of digits.
DECLARE @i TINYINT;
DECLARE cursor_numbers CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT Number = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10;
OPEN [cursor_numbers];
FETCH NEXT FROM [cursor_numbers] INTO @i;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @i;
FETCH NEXT FROM [cursor_numbers] INTO @i;
END
CLOSE [cursor_numbers];
DEALLOCATE [cursor_numbers];
Looping over Database Objects
Sometimes you need to loop over database or server objects instead of actual data. Some examples:
- you want to reorganize all indexes in a database
- you want to take a backup of all user databases
- you want to truncate a subset of tables in a database
All these use cases can be implemented with a cursor or a WHILE loop and the use of system views. Let’s truncate all the fact tables in the Adventure Works data warehouse sample database. First we extract the list of tables from the system views sys.tables and sys.schemas and we construct the TRUNCATE TABLE statement:
SELECT
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,SQLStatement = CONCAT('TRUNCATE TABLE ' ,s.[name],'.', t.[name],';')
FROM sys.[tables] t
JOIN sys.[schemas] s ON [s].[schema_id] = [t].[schema_id]
WHERE [type] = 'U' -- user tables
AND t.[name] LIKE 'Fact%';
The ROW_NUMBER function is used to generate a unique ID for each row.
To actually execute these statements, we can use a WHILE loop. First, we insert the data into a temp table and then we loop over this table. Using EXEC or sp_executesql we can then execute each individual statement. This pattern is called “dynamic SQL” since the SQL statements are generated on-the-fly using metadata.
DROP TABLE IF EXISTS #statements;
SELECT
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,SQLStatement = CONCAT('TRUNCATE TABLE ' ,s.[name],'.', t.[name],';')
INTO #statements
FROM sys.[tables] t
JOIN sys.[schemas] s ON [s].[schema_id] = [t].[schema_id]
WHERE [type] = 'U' -- user tables
AND t.[name] LIKE 'Fact%';
DECLARE @stmt VARCHAR(500);
DECLARE @i TINYINT = 1;
DECLARE @n TINYINT;
SELECT @n = MAX(ID)
FROM [#statements];
WHILE @i <= @n
BEGIN
SELECT @stmt = [SQLStatement]
FROM [#statements]
WHERE ID = @i;
PRINT @stmt;
--EXEC sp_executesql @stmt; --> actually truncate table
SET @i += 1;
END
I commented out the actual execution of the dynamic SQL, so you can test the script first before you empty out all your fact tables.
There are two undocumented stored procedures which allow you to do the exact same thing, but without you needing to write a cursor or a WHILE loop: sp_msforeachtable and sp_msforeachdb. With the following script, we select the row count of each fact table:
DECLARE @cmd1 NVARCHAR(1000);
SET @cmd1 = 'IF ''?'' LIKE ''%Fact%'''
+ 'BEGIN '
+ 'SELECT TableName = ''?'', RowCnt = COUNT(1) FROM ?; '
+ 'END';
EXEC sp_msforeachtable @cmd1;
- since we are concatenating strings into a SQL statement, we need to assign this to a variable before we can use it in a stored procedure.
- the question mark is a placeholder for the table name (which includes the schema)
- we can filter the tables by using an IF statement
Executing the script gives the following results:
As you can see, the table names includes the schema name and the table name, both enclosed in square brackets. To learn more about sp_msforeachtable and sp_msforeachdb, check out the tip Iterate through SQL Server database objects without cursors.
The Tally Table or Numbers Table
The tally table, or the “numbers” table, is a table with one column containing only sequential numbers, usually starting at one. Using the ROW_NUMBER function and common table expressions, we can quickly generate such a table:
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1)
,E02(N) AS (SELECT 1 FROM E00 a, E00 b)
,E04(N) AS (SELECT 1 FROM E02 a, E02 b)
,E08(N) AS (SELECT 1 FROM E04 a, E04 b)
,E16(N) AS (SELECT 1 FROM E08 a, E08 b)
,E32(N) AS (SELECT 1 FROM E16 a, E16 b)
,cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E32)
SELECT N
FROM cteTally
WHERE N <= 10000;
This script quickly generates 10,000 rows each with a unique sequential number:
How can we use this to implement a FOR loop? You can look at each individual row of the numbers table is an iteration of a loop. So, the first row with number 1 is the first iteration (@i = 1), while the second row with number 2 is the second iteration (@i = 2) and so on. In fact, our very first WHILE loop where we printed out 11 numbers can be implemented by the same script that generated the tally table. In this case we printed out the numbers till the number 10,000.
Using the numbers table, we can mimic a FOR loop by using the numbers of the table as an indicator of the different iterations. Let’s illustrate by splitting out the sentence “Hello MSSQLTips!” into individual characters. The very first row of the numbers table with the value 1 will select the first character, the second row the second character and so on.
We can accomplish this using the SUBSTRING function, where we select the Nth character (where N is the number of the current iteration) with a length of 1. By using the TOP clause, we limit the records of the number table to the number of characters found in the string.
DECLARE @mystring VARCHAR(100) = 'Hello MSSQLTips!';
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1)
,E02(N) AS (SELECT 1 FROM E00 a, E00 b)
,E04(N) AS (SELECT 1 FROM E02 a, E02 b)
,E08(N) AS (SELECT 1 FROM E04 a, E04 b)
,E16(N) AS (SELECT 1 FROM E08 a, E08 b)
,cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@mystring),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT N, myChar = SUBSTRING(@mystring,N,1)
FROM cteTally;
The result:
The advantage of the numbers table is that everything is set-based (meaning we operate on columns and not on individual rows) which is very fast in SQL Server. In many cases, a proper set-based solution will outperform a WHILE loop or a cursor. However, you can only use the numbers table in cases where you need to loop over data. If you want to loop over database objects and use dynamic SQL like in the previous paragraph, you’ll need to resort to a cursor or WHILE loop (or the system stored procs). Luckily in those cases the data sets are small and there’s no real performance impact.
Next Steps
- For more info about cursors versus WHILE loops, check out the following tips:
- Also check out this blog post by Aaron Bertrand about performance of cursors and WHILE loops: Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR
- More tips about the tally table:
