October 22, 2021

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.  Start learning SQL today using these free tools with my guide Getting Started Using SQL Server.

WHILE Statement, BREAK, and CONTINUE

The WHILE statement repeatedly executes a block of SQL statements.  This is called a loop, and it continues until as long as the 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 it Work?

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:

WHILE Statement Example 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

BREAK is used to forcibly exit from a WHILE statement’s code block.

In the following example we’ve altered the WHILE loop to use the BREAK command (colored green).

There are two changes:

  1. The WHILE condition always evaluates to TRUE, as @n is always greater than or equal to zero.
  2. The IF ELSE 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 command is useful for ending execution.  If WHILE blocks 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, PRINT 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.

While Statement – Dig Deeper:

Check out these articles to learn more about about T-SQL control of flow statements:

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>