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:
|
1 2 3 4 |
WHILE condition BEGIN //SQL Statements END; |
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:
|
1 2 3 4 5 6 7 8 |
DECLARE @count INT; SET @count = 1; WHILE @count<= 5 BEGIN PRINT @count SET @count = @count + 1; END; |
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:
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”:
|
1 |
CREATE 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:
|
1 2 3 4 5 6 7 |
USE CarShop CREATE TABLE Cars ( Id INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR (50) NOT NULL, Price INT ) |
Let’s now use the While loop in SQL to insert 10 records in the Cars table. Execute the following script:
|
1 2 3 4 5 6 7 8 |
DECLARE @count INT; SET @count = 1; WHILE @count<= 10 BEGIN INSERT INTO Cars VALUES('Car-'+CAST(@count as varchar), @count*100) SET @count = @count + 1; END; |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @count INT DECLARE @limit INT; SET @count = 0 SET @limit = 2; WHILE @count< 10 BEGIN SELECT * FROM Cars ORDER BY Id OFFSET @count ROWS FETCH NEXT @limit ROWS ONLY SET @count = @count + 2; END; |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @count INT; DECLARE @mod INT; SET @count = 1; WHILE @count<= 10 BEGIN set @mod = @count % 2 IF @mod = 1 BEGIN SET @count = @count + 1; CONTINUE END PRINT @count SET @count = @count + 1; END; |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @count INT; SET @count = 1; WHILE @count<= 10 BEGIN IF @count > 5 BEGIN BREAK END PRINT @count SET @count = @count + 1; END; |
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.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021