SQL Server FOR Loop Syntax

Syntax:

FOR variable IN (SELECT expression FROM table_name WHERE condition)
BEGIN
    -- Loop body
END;

Explanation:

1. FOR variable IN (SELECT expression FROM table_name WHERE condition):

   • variable: A temporary variable declared within the loop to store the value retrieved from the query.
   • SELECT expression FROM table_name WHERE condition: A SELECT statement that defines the data to be iterated over.
     • expression: The column or expression to be assigned to the variable in each iteration.
     • table_name: The name of the table from which the data is fetched.
     • WHERE condition: An optional condition to filter the rows retrieved from the table.

Example:

DECLARE @counter INT;

FOR @counter IN (SELECT number FROM numbers WHERE number <= 10)
BEGIN
    PRINT @counter;
END;

This example will print the numbers from 1 to 10 to the console.

Key Points:

• The WHERE clause can be used to filter the rows that are iterated over.
• The loop body is executed once for each row returned by the SELECT statement.
• The variable declared within the loop can be used to access the current value in each iteration.
• The FOR loop in SQL Server is primarily used for iterating over result sets from SELECT statements.

Additional Notes:

• For more complex iteration scenarios, consider using cursor-based mechanisms or procedural language extensions like T-SQL.
• While the FOR loop is useful for certain scenarios, it's generally recommended to use set-based operations (like joins, unions, and aggregations) whenever possible for better performance and readability in SQL Server.

Understanding SQL Server FOR Loops with Examples

Basic FOR Loop Example:

This example iterates through a list of numbers and prints each one:

DECLARE @counter INT;

FOR @counter IN (SELECT number FROM numbers WHERE number <= 10)
BEGIN
    PRINT @counter;
END;

• PRINT @counter;: Prints the current value of @counter in each iteration.
• FOR @counter IN (SELECT number FROM numbers WHERE number <= 10): Iterates over the numbers from 1 to 10.
• DECLARE @counter INT;: Declares a variable named @counter to store the current number.

Iterating Over a Table's Data:

This example iterates through each row in the Employees table and prints the employee's name:

DECLARE @employeeName VARCHAR(50);

FOR @employeeName IN (SELECT FirstName + ' ' + LastName FROM Employees)
BEGIN
    PRINT @employeeName;
END;

• PRINT @employeeName;: Prints the employee's name in each iteration.
• FOR @employeeName IN (SELECT FirstName + ' ' + LastName FROM Employees): Iterates over each row in the Employees table and concatenates the first and last names.
• DECLARE @employeeName VARCHAR(50);: Declares a variable to store the employee's name.

Using a WHERE Clause:

This example iterates through employees whose salary is greater than $50,000:

DECLARE @employeeName VARCHAR(50);

FOR @employeeName IN (SELECT FirstName + ' ' + LastName FROM Employees WHERE Salary > 50000)
BEGIN
    PRINT @employeeName;
END;

• WHERE Salary > 50000: Filters the results to only include employees with a salary greater than $50,000.

Nested FOR Loops:

You can nest FOR loops within each other to perform more complex iterations:

DECLARE @outerLoop INT;
DECLARE @innerLoop INT;

FOR @outerLoop IN (SELECT number FROM numbers WHERE number <= 5)
BEGIN
    FOR @innerLoop IN (SELECT number FROM numbers WHERE number <= 3)
    BEGIN
        PRINT @outerLoop + ' - ' + @innerLoop;
    END;
END;

This example will print combinations of numbers from 1 to 5 and 1 to 3.

Alternative Methods to FOR Loops in SQL Server

While the FOR loop can be useful in certain scenarios, there are often more efficient and readable alternatives available in SQL Server. Here are some common approaches:

Set-Based Operations

• GROUP BY and HAVING: Aggregate data and filter based on aggregated values.
• UNION and UNION ALL: Combine results from multiple SELECT statements.
• JOINs: Combine data from multiple tables based on related columns.

Instead of using a FOR loop to iterate through a table and calculate the sum of a column, you can use SUM aggregation:

SELECT SUM(column_name)
FROM table_name;

Window Functions

• PARTITION BY and ORDER BY: Define partitions and the order within partitions.
• LEAD and LAG: Access rows before or after the current row.
• RANK, DENSE_RANK, ROW_NUMBER: Assign a rank or sequence number to rows within a partition.

To calculate the running total of a column, use the SUM window function:

SELECT column_name,
       SUM(column_name) OVER (ORDER BY column_order) AS running_total
FROM table_name;

Common Table Expressions (CTEs)

• Recursive queries: Create recursive CTEs to process hierarchical data.
• Temporary result sets: Define temporary result sets that can be referenced multiple times within a query.

To generate a list of numbers from 1 to 10 using a recursive CTE:

WITH Numbers AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1
  FROM Numbers
  WHERE number < 10
)
SELECT number
FROM Numbers;

Stored Procedures and Functions

• Performance optimization: Compile stored procedures and functions for improved performance.
• Modularize code: Encapsulate reusable logic into stored procedures or functions.

Create a stored procedure to calculate the average salary of employees:

CREATE PROCEDURE CalculateAverageSalary
AS
BEGIN
  SELECT AVG(Salary) AS AverageSalary
  FROM Employees;
END;

Choosing the Right Approach:

The best method for a particular task depends on factors like:

• Readability: Choose the approach that is most understandable to you and others.
• Complexity: Recursive CTEs and stored procedures can handle complex logic.
• Data volume: For large datasets, set-based operations and window functions often perform better.