While Loop in SQL Server Stored Procedure

In this SQL Server tutorial, I will explain the while loop in the SQL Server stored procedure.

Whenever I am required to execute complex logic for many records in a table, I use the while loop to avoid writing this logic again and again; I encapsulate this logic into a stored procedure, so whenever I need it, I just call this procedure.

This reduces my time and effort. In this tutorial, I have explained the while loop and stored procedure and how you can encapsulate the while loop within the stored procedure for repetitive tasks or logic that you want to execute repeatedly.

You will learn this concept by applying it using real-world examples.

So, let’ start,

Table of Contents

While Loop in SQL Server Stored Procedure

First, you must know what a while loop is and how it works. The while loop in SQL Server executes the block of code repeatedly based on the specified boolean condition.

The while loop runs until the boolean condition becomes false, and as soon as the boolean condition becomes false, the while loop is terminated.

The syntax is given below.

WHILE boolean_condition
BEGIN
    -- Statements that you want to execute
END

Where,

boolean_condition: It is the expression that returns true or false. This condition is evaluated before starting the next iteration.
- If the condition is true, then the block of statements within BEGIN and END is executed,
- Otherwise, if the condition is false, it breaks the execution or exits the loop. If any code exists after the END keyword, it begins to execute that code.

The BEGIN…END section is where you write your SQL statement, query, or whatever operation you want to perform on the database.

Now that you know how to use the while loop, let’s learn about stored procedures. A stored procedure is a way to store the database logic that you want to execute repeatedly.

Suppose you have complex database logic that you need to execute usually, so instead of writing this logic whenever required, you keep the logic in something called stored procedure, then call this stored procedure whenever you need that logic.

If you have any doubts related to the stored procedure, then you can refresh your knowledge about the stored procedure by reading this tutorial, Create Stored Procedure in SQL Server.

Here, we will build a stored procedure to update the inventory, such as the product quantity. So whenever we need to update the product quantity, we will call this stored; that’s it.

First, create a table named Products with columns ID and Quantity which is shown below.

CREATE TABLE Products (
    ID INT PRIMARY KEY,
    Quantity INT
);

Insert the 10 different records into the table Products using the query below.

INSERT INTO Products (ID, Quantity) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 250),
(5, 300),
(6, 350),
(7, 400),
(8, 450),
(9, 500),
(10, 550);

View the Products table using the query below.

SLELECT * FROM Products;

Creating Products Table Before Creating While Loop in SQL Server Stored Procedure

From the above picture, you can see that the Products table contains the records of the 10 products and their quantities.

Now, suppose you need to create a stored procedure named UpdateInventoryQuantity. This stored procedure should contain a while loop to add the 10 quantities to each product.

You can create that kind of stored procedure using the command below.

CREATE PROCEDURE UpdateInventoryQuantity
AS
BEGIN
    DECLARE @ProductID INT = 1

    
    WHILE @ProductID IS NOT NULL
    BEGIN
       
        UPDATE Products
        SET Quantity = Quantity + 10
        WHERE ID = @ProductID

        SELECT @ProductID = MIN(ID) FROM Products WHERE ID > @ProductID
    END
END
GO

While Loop in SQL Server Stored Procedure

From the above output, you have successfully created the stored procedure containing the while loop to update the quantity of each product by 10 each time whenever you call this stored procedure.

Let’s understand the code part with BEGIN…END section.

This line DECLARE @ProductID INT = 1 creates a new variable named ProductID of type integer and initializes it with value 1.

Then, WHILE LOOP begins with boolean conditions @ProductID IS NOT NULL. This means the loop runs as long as ProductID is not equal to NULL. Meanwhile, it does the following things.

It begins the update on the Products table using the ‘UPDATE Products’ statement and adds the 10 quantity to the value of the column ‘Quantity’ using the statement ‘SET Quantity = Quantity + 10’.
It increases the quantity of the product where ID is equal to ProductID using the statement ‘WHERE ID = @ProductID’.

Then, the next line of code, ‘SELECT @ProductID = MIN(ID) FROM Products WHERE ID > @ProductID’, fetches the next ID of the product from the table and assigns this ID to the variable @ProductID.

But how does it fetch the ID of the next product? It does so by comparing the current ID with the next ID. If the next ID is greater than the current ID, it assigns it to the variable ProductID.

This is how the while loop with the stored procedure is used to update the quantity of the product.

To add the 10 quantities to each product, just call this procedure using the code below.

EXEC UpdateInventoryQuantity;

Calling While Loop in SQL Server Stored Procedure

Look at the output; when you call the store procedure UpdateInventoryQuantity, it shows the number of rows updated, as you can see in the output.

Now, let’s check the Products table to see whether the quantity has increased by 10.

SELECT * FROM Products

Checking Products Table After Calling While Loop in SQL Server Stored Procedure

From the output, you can see that each product quantity is increased by 10 if you compare it with the previous picture that is shown at the beginning of this tutorial while creating the table.

Well, from the above example, I hope that you understand how to use the while loop in stored procedure to perform repetitive operations on the database or table.

Conclusion

In this SQL Server tutorial, you learned about the while loop in the SQL Server store procedure. You covered the while loop and its syntax and also learned about the fundamental concept of a stored procedure.

Then, you learned how to use the while loop within the stored procedure by updating the product quantity by 10.