In programming, a loop allows you to write a set of code that will run repeatedly within the same program. Many programming languages have several different types of loop to choose from, but in SQL Server there is only one: the WHILE loop.
If you find yourself frequently (more than once!) writing loops in SQL, you're probably approaching things the wrong way. The online version of our two-day advanced SQL course can be taken wherever you are in the world, and will explain how to program effectively in SQL (you can see details of all of our SQL training here).
The Basic Syntax of a WHILE Loop
Before we do anything useful with a loop, let's look at the basic syntax. The code below shows the main elements you'll need to get a loop working:
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <= 10
BEGIN
SET @Counter += 1
END
The comments in the code above explain what is going on at each stage of the procedure. For our example we've used a counter to keep track of how many times the loop has run. In this case the loop will continue to run while the value of the counter is 10 or less.
You can happily execute the code shown above and the procedure will run, it just doesn't provide any kind of result! To prove that the loop is running the specified number of times we can add a simple statement to print the value of the counter variable:
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <= 10
BEGIN
PRINT @Counter
SET @Counter += 1
END
The impressive result of the procedure is shown below:
Ok, so it's still pretty useless, but it demonstrates that the loop is working.
Now that we've got the basic loop working, the next part of this series shows how to run a query inside a loop.
This part of the series will show you how to use a loop to execute a query multiple times, using a different value in the WHERE clause of the query each time.
Our Example
To demonstrate this technique we're going to list the number of films which have won different numbers of Oscars. The output we're aiming for is shown in the diagram below:
Each time the query is executed it generates a new line in the output.
Declaring and Initialising the Variables
For this example we'll use three variables, as shown below. If you need a reminder of how variables work have a read of this blog first.
DECLARE @Counter INT
DECLARE @MaxOscars INT
DECLARE @NumFilms INT
The @Counter variable is used to keep track of the number of times the loop has run, as it did in the previous example. The @MaxOscars variable is used to hold the highest number of Oscar wins for a single film, which will tell us when to stop looping. The @NumFilms variable is used to count how many films have won the specified number of Oscars each time we go through the loop.
Once the variables have been declared we can set the values for two of them like so:
SET @Counter = 0
SET @MaxOscars = (SELECT MAX(FilmOscarWins) FROM tblFilm)
Our loop counter will begin at 0 and the highest number of Oscars won by a single film can be found with the simple query shown above.
Defining the Loop
The next step is to lay out the structure of the loop itself. Here's the code to do just that:
WHILE @Counter <= @MaxOscars
BEGIN
SET @Counter += 1
END
This loop will continue to run as long as the value of the loop counter is less than the value stored in the @MaxOscars variable. We've made sure to include the line which increments the counter as well.
Running a Query Inside the Loop
With the structure of the procedure defined, we can now add the code which will be executed each time we go through the loop. Firstly, we want to count how many films have won the specified number of Oscars. We can do that using the COUNT function and storing the result in our @NumFilms variable. Here's how that might look within the loop:
WHILE @Counter <= @MaxOscars
BEGIN
SET @NumFilms =
(
SELECT COUNT(*)
FROM tblFilm
WHERE FilmOscarWins = @Counter
)
SET @Counter += 1
END
Displaying the Result
Each time we calculate the number of films winning the specified number of Oscars we need to display the result somewhere. We can do this using the PRINT statement combined with a concatenated message, like so:
WHILE @Counter <= @MaxOscars
BEGIN
SET @NumFilms =
(
SELECT COUNT(*)
FROM tblFilm
WHERE FilmOscarWins = @Counter
)
PRINT
CAST(@NumFilms AS VARCHAR(3)) +
' films have won ' +
CAST(@Counter AS VARCHAR(2)) +
' Oscars.'
SET @Counter += 1
END
Now that we've added all of the code we can execute the procedure to return the output shown at the top of the page.
Was it Worth it?
For such a simple example, almost certainly not! We could have achieved almost the same results using a single SELECT statement with a GROUP BY clause.
SELECT
FilmOscarWins
,COUNT(*) AS [NumberOfFilms]
FROM
tblFilm
GROUP BY
FilmOscarWins
The output of the query is shown below:
It's not quite as nicely presented, but it's basically the same set of results.
If you can achieve the same results using either a set-based operation or a loop, the set-based method will almost certainly be quicker (our two-day advanced SQL course explains the set-based alternatives possible). There will be times, however, when a loop is pretty much the only way to get the job done. This is particularly true when you're working with cursors.
Cursors are described in detail in another blog. For the final part of this series we're going to show you how to break out of a loop prematurely, and what to do if you ever get stuck in a dreaded endless loop!
When you've written the code to begin a loop you can add further conditions
to make the loop end prematurely.
The BREAK Statement
To prematurely end a loop you can add a BREAK statement to
your code. You would normally do this within an IF
statement. In the example below, if we find that the number of films
winning a certain number of Oscars falls to zero we will exit the loop:
DECLARE @Counter INT
DECLARE @MaxOscars INT
DECLARE @NumFilms INT
SET @Counter = 0
SET @MaxOscars = (SELECT MAX(FilmOscarWins) FROM tblFilm)
WHILE @Counter <= @MaxOscars
BEGIN
SET @NumFilms =
(
SELECT COUNT(*)
FROM tblFilm
WHERE FilmOscarWins = @Counter
)
IF @NumFilms = 0
BEGIN
PRINT
'The first group with 0 films is ' +
CAST(@Counter AS VARCHAR(2)) + ' Oscars'
BREAK
END
PRINT
CAST(@NumFilms AS VARCHAR(3)) +
' films have won ' +
CAST(@Counter AS VARCHAR(2)) +
' Oscars.'
SET @Counter += 1
END
The output from this code is shown in the diagram below:
When we execute the code we find that it ends on the tenth pass through the loop, when we find that no films won 9 Oscars.
Using the BREAK statement means that you can give your loops
multiple exit routes. But what happens if you've written a loop which
doesn't have any exit points at all; the so-called endless loop?
Getting Stuck in an Endless Loop
Getting stuck in an endless loop is embarrassingly easy; all you need
to do is write a loop which never finds an exit point. In the code below
we've "accidentally" forgotten to add the line which increments our loop
counter:
DECLARE @Counter INT
DECLARE @MaxOscars INT
DECLARE @NumFilms INT
SET @Counter = 0
SET @MaxOscars = (SELECT MAX(FilmOscarWins) FROM tblFilm)
WHILE @Counter <= @MaxOscars
BEGIN
SET @NumFilms =
(
SELECT COUNT(*)
FROM tblFilm
WHERE FilmOscarWins = @Counter
)
PRINT
CAST(@NumFilms AS VARCHAR(3)) +
' films have won ' +
CAST(@Counter AS VARCHAR(2)) +
' Oscars.'
END
We can set this code running but we'll never see it reach a natural end.
The code is running, but we'll be waiting for a long time if we're expecting it to finish!
When you realise that your code is stuck in an endless loop you can attempt to
force it to end by clicking the tool shown in the diagram below:
Click here, or press ALT + BREAK on the keyboard.
Once you've managed to break into your code you can then go about ensuring that
the loop has an end point before running it again!
Hopefully we'll see more useful results after we've tweaked the code.
