Loops in SQL Server

Loops in SQL Server

Introduction

In SQL Server, loops are used to perform repetitive tasks based on certain conditions or a fixed number of iterations. While SQL is primarily a declarative language, meaning you specify what you want to retrieve without explicitly stating how to do it, loops can be implemented using procedural constructs within SQL Server.

Types of Loops in SQL Server

There are mainly two types of loops that can be implemented in SQL Server:

1. WHILE Loop:
   The WHILE loop is used to execute a block of SQL statements repeatedly as long as a specified condition is true.

Syntax:

sql
WHILE condition
BEGIN
    -- SQL statements to be executed repeatedly
END

Example:
Let's create a simple WHILE loop that prints numbers from 1 to 5.

sql
DECLARE @counter INT = 1;

WHILE @counter <= 5
BEGIN
    PRINT @counter;
    SET @counter = @counter + 1;
END

Explanation:
In this example, we declare a variable @counter and initialize it to 1. The WHILE loop continues to execute the block of code inside as long as the condition @counter <= 5 is true. Within the loop, we print the current value of @counter and then increment it by 1 in each iteration using SET @counter = @counter + 1;.

2. CURSOR Loop:
   A CURSOR loop is used to iterate over the rows returned by a SELECT statement and process each row individually.

Syntax:

sql
DECLARE cursor_name CURSOR FOR
SELECT column1, column2, ...
FROM your_table
WHERE condition;

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- SQL statements to process the current row
    FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

Example:
Let's create a CURSOR loop that calculates the total salary of all employees.

sql
DECLARE @emp_id INT;
DECLARE @salary DECIMAL(10, 2);
DECLARE total_salary DECIMAL(10, 2) = 0;

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, Salary
FROM EmployeeTable;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @emp_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET total_salary = total_salary + @salary;
    FETCH NEXT FROM employee_cursor INTO @emp_id, @salary;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

PRINT 'Total Salary: ' + CAST(total_salary AS NVARCHAR);

Explanation:
In this example, we declare variables @emp_id and @salary to store the values fetched from the cursor. We then create a cursor named employee_cursor that selects the EmployeeID and Salary from the EmployeeTable. The OPEN statement opens the cursor, and we use FETCH NEXT to retrieve the first row of data into the variables. The loop continues as long as @@FETCH_STATUS is 0, meaning there are more rows to process. Within the loop, we accumulate the total salary of all employees. Finally, we print the total salary using the PRINT statement.

Conclusion

Loops in SQL Server, particularly the WHILE loop and the CURSOR loop, allow you to perform iterative operations on data based on specified conditions. However, it is essential to use loops judiciously in SQL and explore other set-based operations whenever possible, as loops can impact performance when dealing with large datasets.