This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Writing Loops in SQL Running SQL Queries in a Loop (this blog) Ending Loops and Endless Loops This blog is part of a complete SQL tutorial. We also run classroom-based SQL training courses.

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!

Writing Loops in SQL
Running SQL Queries in a Loop (this blog)
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

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:

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

)

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!

Parts of this blog
Writing Loops in SQL Running SQL Queries in a Loop (this blog) Ending Loops and Endless Loops

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Running SQL Queries in a Loop

Our Example

Declaring and Initialising the Variables

Defining the Loop

Running a Query Inside the Loop

Displaying the Result

Was it Worth it?

Head office

London

Manchester