Loops in SQL
Part two of a three-part series of blogs

Many programming languages feature a variety of types of loop which allow your programs to repeat a set of instructions multiple times. In SQL there is only one type of loop, and this blog explains how it works!

  1. Writing Loops in SQL
  2. Running SQL Queries in a Loop (this blog)
  3. Ending Loops and Endless Loops

This blog is part of a complete SQL tutorial.  We also run classroom-based SQL training courses.

Posted by Andrew Gould on 18 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Running SQL Queries in 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:

Output of query

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:

Output of select

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 and that's what you should use.  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!

  1. Writing Loops in SQL
  2. Running SQL Queries in a Loop (this blog)
  3. Ending Loops and Endless Loops
This blog has 0 threads Add post