560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
This blog is part of a complete SQL tutorial. We also run classroom-based SQL training courses. |
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).
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.