BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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 (this blog)
- Running SQL Queries in a Loop
- 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.
If you find yourself frequently (more than once!) writing loops in SQL, you're probably approaching things the wrong way. Our live online 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:
--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:
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.
- Writing Loops in SQL (this blog)
- Running SQL Queries in a Loop
- Ending Loops and Endless Loops