Sorry for the mess. We are working on getting this page updated as soon as possible. Thank you for your patience!

Loops in SQL

Written by

Carissa O'Connell

November 20, 2022; Updated: Jan 2023

SQL loops are used to execute a set of statements repeatedly for a specified number of times. They are essential in situations where a task needs to be performed multiple times, such as updating inventory levels for multiple products or generating reports for different time periods. SQL loops simplify the process of performing repetitive tasks, reducing the risk of errors and improving overall efficiency.

While Loop

A SQL WHILE loop is a control-flow statement that allows code to execute repeatedly based on a given Boolean condition. The loop continues until the condition becomes false.

DECLARE @counter INT = 1; -- Initialize counter
WHILE @counter <= 10 -- Condition for the loop
BEGIN
	PRINT 'Inside WHILE loop'; -- Code to be executed
	SET @counter = @counter + 1; -- Increment counter
END;
PRINT 'Done with WHILE loop';
						

For Loop

A For Loop is used to execute a set of statements for a specified number of times. It is commonly used to iterate through a range of values or to perform a task for each item in a list.

Example

Suppose we want to update the inventory levels of 10 different products in our prescription inventory management system. We can use a For Loop to iterate through the list of products and update their inventory levels accordingly.

DECLARE @product_id INT = 1; 
WHILE @product_id <= 10 
BEGIN
	UPDATE products
	SET inventory_level = inventory_level - 1
	WHERE product_id = @product_id;
	SET @product_id = @product_id + 1;
END;
					

Cursor Loop

A Cursor Loop is used to iterate through a set of rows returned by a query. It is commonly used to perform a task for each row in a result set.

Example

Suppose we want to generate a report of all products with low inventory levels in our prescription inventory management system. We can use a Cursor Loop to iterate through the result set and generate the report.

-- 1 - Declare Variables
DECLARE @medication_id INT;
DECLARE @medication_name VARCHAR(50);
DECLARE @current_quantity INT;

-- 2 - Declare Cursor
DECLARE cur CURSOR FOR
SELECT medication_id, medication_name, current_quantity
FROM medications
WHERE current_quantity < reorder_quantity;

-- Open Cursor
OPEN cur;

-- 3 - Fetch the next record from the cursor
FETCH NEXT FROM cur INTO @medication_id, @medication_name, @current_quantity;

-- Set the status for the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
	-- 4 Begin the custom business logic
	PRINT 'Medication ID: ' + CONVERT(VARCHAR(10), @medication_id) + ', Medication Name: ' + @medication_name + ', Current Quantity: ' + CONVERT(VARCHAR(10), @current_quantity);
	-- 5 - Fetch the next record from the cursor
	FETCH NEXT FROM cur INTO @medication_id, @medication_name, @current_quantity;
END;

-- 6 - Close cursor
	CLOSE cur;
-- 7 - Deallocate cursor
	DEALLOCATE cur;
					

When could a SQL Cursor be used?

• Iterating over data one row at a time
• Completing a process in a serial manner, such as SQL Server database backups
• Updating data across numerous tables for a specific account
• Correcting data with a predefined set of data as the input to the cursor

Reference: My SQL Tips

IF... ELSE IF and PRINT

The IF...ELSE statement allows you to execute a block of code based on whether a specified condition evaluates to true or false. The IF...ELSE statement is essential for controlling the flow of execution based on specific conditions

-- DECLARE @MedicationName VARCHAR(50)
DECLARE @CurrentStockLevel INT
DECLARE @ReorderThreshold INT

SET @MedicationName = 'Lisinopril'
SET @CurrentStockLevel = 50
SET @ReorderThreshold = 20

IF (@CurrentStockLevel < @ReorderThreshold)
BEGIN
PRINT 'Reorder ' + @MedicationName + ' immediately. Current 
stock level is ' + CONVERT(VARCHAR, @CurrentStockLevel) + '.'
END

ELSE IF (@CurrentStockLevel < (@ReorderThreshold * 2))
BEGIN
    PRINT 'Monitor ' + @MedicationName + ' stock level closely. Current stock level is ' + CONVERT(VARCHAR, @CurrentStockLevel) + '.'
END

ELSE IF (@CurrentStockLevel < (@ReorderThreshold * 3))

BEGIN
    PRINT 'Review ' + @MedicationName + ' stock level periodically. Current stock level is ' + CONVERT(VARCHAR, @CurrentStockLevel) + '.'
END

ELSE
BEGIN
    PRINT 'No action required for ' + @MedicationName + '. Current stock level is ' + CONVERT(VARCHAR, @CurrentStockLevel) + '.'
END

					

Break Statement

Continue Statement

Read Records through While Loop

SQL Server Loop