After reading this article you will understand the basics of using the WHILE statement to write a loop within a stored procedure.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools with my Guide Getting Started Using SQL Server.
WHILE, BREAK, and CONTINUE
The WHILE
statement is used to repeatedly execute a block of SQL statements. The block is repeatedly executed if the WHILE
statement’s condition is true.
The WHILE
statements general format is:
WHILE condition
BEGIN
-- code block run when condition is TRUE
END
If you find yourself repeating statements, especially those that fall into a pattern, then, there’s a possibility you can use a WHILE
statement to save some typing and make your program more fun to write!
For example, let’s assume you need to create a temporary table containing the beginning date of each week. Of course, we could write 52 individual INSERT
statements, but that is boring!
INSERT INTO @myTable VALUES (0, 12/31/2017)
INSERT INTO @myTable VALUES (1, 01/07/2018)
INSERT INTO @myTable VALUES (2, 01/14/2018)
…
INSERT INTO @myTable VALUES (52, 12/30/2018 )
Instead use the WHILE
command to loop through the 52 weeks, inserting each week’s beginning date into the table. You can see how we do this in the following example.
--Setup Variables
DECLARE @myTable TABLE(WeekNumber int,
DateStarting smalldatetime)
DECLARE @n int = 0
DECLARE @firstWeek smalldatetime = '12/31/2017'
--Loop Through weeks
WHILE @n <= 52
BEGIN
INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek));
SELECT @n = @n + 1
END
--Show Results
SELECT WeekNumber, DateStarting
FROM @myTable
What makes this tick is the DATEADD
function. Notice that DATEADD(wk, @n, @firstWeek)
adds a week’s work of days to our @firstWeek
date. In the first iteration it adds 0 weeks. In the second iteration, @n = 1
, so it adds 1 week, and so on.
Here are some more points:
- The
WHILE
statement tests the variable @n
. If it is <= 52, the program block (green), can run. - Each time the block is run, the variable @n is incremented by one. This is important. If this didn’t happen, the value would never be greater than 52, and our program would execute the code block, on and on, without end. This is called an infinite loop.
- Reinforcement to #2 above! It is important your loop has an end condition. In our case we make sure that variable
@n
is incremented, and that it will eventually be greater than 52. - We use the value
@n
to “drive” the date value. By adding weeks to our base date, @firstWeek
, we can calculate subsequent beginning week’s dates.
Here are the results:
As you can see, from this simple loop, we were able to create some interesting data, and it was more fun to do wo using date functions, than to “hard code” the statement. Using loops are very powerful.
Using BREAK to Short Circuit a Loop
The BREAK
statement is used to forcibly exit from a WHILE
statement’s code block.
In the following example we’ve altered the WHILE
statement. It now uses the BREAK
statement, which is colored in green.
There are two changes:
- The
WHILE
Statement condition always evaluates to TRUE
, as @n
is always greater than or equal to zero. - The
IF
Statement tests for the end condition, and when TRUE
, executes BREAK
.
DECLARE @myTable TABLE(WeekNumber int,
DateStarting smalldatetime)
DECLARE @n int = 0
DECLARE @firstWeek smalldatetime = '12/31/2017'
WHILE @n > -1
BEGIN
INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek));
SELECT @n = @n + 1
IF @n > 52 BREAK
END
The BREAK
statement is useful for ending execution. If WHILE
statements are nested, then the inner most loop is exited.
Using CONTINUE to Reset a Loop
The CONTINUE
statement restarts a WHILE
statement’s code block. Any statements found after the CONTINUE
aren’t executed.
For example, in the following, the PRINT
statement is never executed.
DECLARE @myTable TABLE(WeekNumber int,
DateStarting smalldatetime)
DECLARE @n int = 0
DECLARE @firstWeek smalldatetime = '12/31/2017'
WHILE @n > -1
BEGIN
INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek));
SELECT @n = @n + 1
IF @n > 52 BREAK
ELSE CONTINUE
PRINT ‘I Never get executed’
END
The reason is either the BREAK
statement force an EXIT
, or when the CONTINUE
is run, the loop is reset to the beginning of the block.