Loops in SQL
Part one 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 (this blog)
  2. Running SQL Queries in a Loop
  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.

Writing Loops in SQL

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.

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:

--This variable keeps track of how many times the loop has run

DECLARE @Counter INT

SET @Counter = 0

--The loop begins by checking a condition is met

--Here we check that the counter has not exceeded 10

WHILE @Counter <= 10

--When the condition is met, the loop is entered

--The BEGIN/END block groups the instructions performed in the loop

BEGIN

--Do something useful here

 

--Increment the counter variable

SET @Counter += 1

END

--After END, the procedure returns to the WHILE line

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:

Basic loop output

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 blog has 0 threads Add post