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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/