Control Flow Statements in Stored Procedures

Control flow statements are essential for writing dynamic and intelligent stored procedures. They allow you to control the order in which statements are executed based on certain conditions or to repeat blocks of code.

1. Conditional Statements (IF...ELSE)

The IF...ELSE statement allows you to execute a block of code only if a specified condition is true. You can optionally include an ELSE block to execute code when the condition is false.

Syntax

IF condition
BEGIN
    -- Statements to execute if the condition is TRUE
END
ELSE
BEGIN
    -- Statements to execute if the condition is FALSE
END
            

Example

IF EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductID AND UnitsInStock > 0)
BEGIN
    PRINT 'Product is in stock.';
END
ELSE
BEGIN
    PRINT 'Product is out of stock.';
END
                

2. CASE Expression

The CASE expression is similar to a multi-way branch. It allows you to evaluate a list of conditions and return one of multiple possible result expressions.

Syntax

CASE input_expression
    WHEN when_expression THEN result_expression
    [...n]
    [ELSE else_result_expression]
END
            

Example

SELECT OrderID,
       CASE OrderStatus
           WHEN 1 THEN 'Pending'
           WHEN 2 THEN 'Processing'
           WHEN 3 THEN 'Shipped'
           WHEN 4 THEN 'Delivered'
           ELSE 'Unknown'
       END AS StatusDescription
FROM Orders;
                

3. Looping Statements

Looping statements are used to execute a block of code multiple times.

WHILE Loop

The WHILE loop executes a statement or a group of statements as long as a specified condition is true.

Syntax

WHILE condition
BEGIN
    -- Statements to execute
    -- Ensure the condition eventually becomes FALSE to avoid infinite loops
END
            

Example

DECLARE @Count INT = 1;
WHILE @Count <= 10
BEGIN
    PRINT 'Processing item number: ' + CAST(@Count AS VARCHAR(10));
    SET @Count = @Count + 1;
END
                

BREAK and CONTINUE

BREAK immediately terminates the innermost WHILE loop. CONTINUE skips the rest of the current iteration and proceeds to the next iteration of the WHILE loop.

Example

DECLARE @Counter INT = 0;
WHILE @Counter < 20
BEGIN
    SET @Counter = @Counter + 1;

    IF @Counter = 5
        CONTINUE; -- Skip to the next iteration when @Counter is 5

    IF @Counter = 15
        BREAK;    -- Exit the loop when @Counter reaches 15

    PRINT 'Current counter value: ' + CAST(@Counter AS VARCHAR(10));
END
                

4. GOTO Statement (Use with Caution)

The GOTO statement transfers control to another statement within the same stored procedure. It's generally recommended to use IF, WHILE, and CASE for better readability and maintainability. Use GOTO sparingly and only when other control structures are not suitable.

Syntax

GOTO label_name;
...
label_name:
    -- Statements to jump to
            

Example

DECLARE @Index INT = 0;

ProcessData:
    SET @Index = @Index + 1;
    PRINT 'Processing step: ' + CAST(@Index AS VARCHAR(10));

    IF @Index < 3
        GOTO ProcessData;

    PRINT 'Processing complete.';
                

Mastering these control flow statements will empower you to write more sophisticated and efficient stored procedures that can handle complex logic and data manipulation.