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:
- WHILE Loop:
The WHILE loop is used to execute a block of SQL statements repeatedly as long as a specified condition is true.
Syntax:
sqlWHILE condition
BEGIN
-- SQL statements to be executed repeatedly
END
Example:
Let's create a simple WHILE loop that prints numbers from 1 to 5.
sqlDECLARE @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;
.
- CURSOR Loop:
A CURSOR loop is used to iterate over the rows returned by a SELECT statement and process each row individually.
Syntax:
sqlDECLARE 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.
sqlDECLARE @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.
0 Comments