Click here to Skip to main content
15,845,436 members
Articles / Productivity Apps and Services / Sharepoint

Using the WHILE Statement in Stored Procedures

Rate me:
Please Sign up or sign in to vote.
2.45/5 (3 votes)
1 Mar 2018MIT3 min read 13.4K   1   3
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.

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:

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

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:

  1. The WHILE Statement condition always evaluates to TRUE, as @n is always greater than or equal to zero.
  2. 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.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
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/

Comments and Discussions

 
GeneralThoughts Pin
PIEBALDconsult1-Mar-18 15:10
mvePIEBALDconsult1-Mar-18 15:10 
GeneralRe: Thoughts Pin
Jeff Moden2-Mar-18 9:16
Jeff Moden2-Mar-18 9:16 
GeneralRe: Thoughts Pin
essentialSQL29-Aug-18 10:54
essentialSQL29-Aug-18 10:54 
The blogs purpose is to illustrate how to use WHILE not to advocate control-of-flow statements over set based operations.
Kris - www.essentialsql.com

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.