Ben Richardson

SQL While loop: Understanding While loops in SQL Server

November 6, 2019 by

The SQL While loop is used to repeatedly execute a certain piece of SQL script.

This article covers some of the basic functionalities of the SQL While loop in Microsoft SQL Server, with the help of examples.

SQL While loop syntax

The syntax of the SQL While loop is as follows:

The while loop in SQL begins with the WHILE keyword followed by the condition which returns a Boolean value i.e. True or False.

The body of the while loop keeps executing unless the condition returns false. The body of a while loop in SQL starts with a BEGIN block and ends with an END block.

A simple example: Printing numbers with SQL While loop

Let’s start with a very simple example where we use a SQL While loop to print the first five positive integer values:

In the script above, we first declare an integer type variable @count and set its value to 5.

Next, we execute a While loop which checks if the value of the @count variable is less than or equals to 5. If the @count variable has a value less than or equals to 5, the body of the loop executes, and the current value of the @count variable is printed on the console.

In the next line, the value of the @count variable is incremented by 1. The While loop keeps executing until the value of the @count variable becomes greater than 5. Here is the output:

Result of variable count calculation

Inserting records with SQL While loop

Let’s now see how the SQL While loop is used to insert dummy records in a database table.

For this we need a simple database “CarShop”:

We will create one table i.e. Cars within the CarShop database. The Cars table will have three columns Id, Name and Price. Execute the following script:

Let’s now use the While loop in SQL to insert 10 records in the Cars table. Execute the following script:

In the script above, we again declare a variable @count and initialize it with 1. Next, a while loop is executed until the value of the @count variable becomes greater than 10, which means that the while loop executes 10 times.

In the body of the while loop, the INSERT query is being used to insert one record into the Cars table. For the Name column, the value of the @count variable is appended with the string Car-. For the Price column of the Cars table, the value of the @count variable is multiplied by 100.

Now if you select all the records from the Cars table with the “SELECT * FROM Cars” query, you should see the following output:

Screenshot of result of using While loop in SQL

Implementing paging with SQL While loop

The while loop can also be used to implement paging. Paging refers to displaying a subset of records from a data table at any particular time.

In the following script, the while loop will be used to select two records from the Cars table at a time. The selected records are then displayed in the console output:

In the script above, we initialize two variables i.e. @count and @limit. The initial values for the @count and @limit variables are 0 and 2, respectively. The while loop executes while the value of the @count variable remains less than 10.

Inside the while loop, the OFFSET clause is used to skip the first N rows of the Cars table. The FETCH NEXT clause fetches the next N records.

In the first iteration, the value of OFFSET will be 0 since @count is 0, the first two records will be displayed. In the second iteration, since the @count variable will have the value 2, the first two records will be skipped and the records 3 and 4 will be retrieved.

In this way, all the records from the Cars table will be retrieved in sets of two. The output is as follows:

Screenshot of result of using while loops to create pagination in SQL

In the output, you can see all the records from the Cars table, printed in sets of two on the console.

The CONTINUE and BREAK statements

The CONTINUE statement is used to shift the control back to the start of a while loop in SQL. The BREAK statement is used to terminate the loop.

The following script shows how to use the CONTINUE statement inside a while loop to print the first five positive even integers:

In the script above, the while loop executes until the value of the @count variable remains less than or equal to 10. The initial value of the @count variable is 1.

In the body of the loop, the value of the remainder of the @count divided by 2 is stored in the @mod variable. If the value of the @count variable is odd, the remainder will be 1, and if the remainder is 0, the CONTINUE statement is used to shift the control back to the start of the while loop and the value of the @count variable is not printed.

Otherwise, the value of the @count variable is printed on the console. Here is the output of the above script:

Result of using count and break statements.

The following example demonstrates the use of a BREAK statement. The while loop in the following script will terminate after printing the first five integers:

Conclusion

If you want to repeatedly execute a particular SQL script, the SQL While loop is the way to go. The article explains how to use the SQL While loop in Microsoft SQL Server to perform a variety of tasks ranging from record insertion to pagination.

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
314 Views