This page has 0 threads | Add post

Blogs

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

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! 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.

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.

Posted by Andrew Gould on 18 February 2013

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. 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).

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.

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

Writing Loops in SQL

The Basic Syntax of a WHILE Loop

Head office

London

Manchester