Stored Procedures - Learning Module

Loading content...

0/241

Control Flow

From Declarative to Procedural

Standard SQL is declarative—you describe the result you want, and the database figures out how to get it. Stored procedures transcend this limitation by adding procedural capabilities: the ability to make decisions, branch execution paths, repeat operations, and iterate through data row by row.

Control flow transforms stored procedures from simple script containers into genuine programs that can handle complex business logic, process data conditionally, and perform operations that pure SQL cannot express.

What You Will Learn

By the end of this page, you will master conditional statements (IF/ELSE, CASE), loop constructs (WHILE, FOR, LOOP), iteration patterns including cursors, control transfer statements (RETURN, BREAK, CONTINUE), and when to use procedural logic versus set-based operations.

IF/ELSE Statements

IF/ELSE statements enable conditional execution—running different code based on whether a condition is true or false. This is the fundamental decision-making construct in procedural SQL.

Structure:

IF condition THEN — Executes if condition is TRUE
ELSEIF/ELSIF condition THEN — Additional conditions (optional)
ELSE — Executes if no conditions matched (optional)
END IF — Terminates the IF block

if_else.sql

-- Basic IF/ELSE syntax in T-SQL
CREATE PROCEDURE Pricing.ApplyDiscount
    @OrderID INT,
    @CustomerTier VARCHAR(20),
    @OrderTotal DECIMAL(18,2),
    @DiscountedTotal DECIMAL(18,2) OUTPUT,
    @DiscountApplied DECIMAL(5,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Simple IF
    IF @OrderTotal <= 0
    BEGIN
        SET @DiscountedTotal = 0;
        SET @DiscountApplied = 0;
        RETURN;
    END
    
    -- IF/ELSE IF/ELSE chain
    IF @CustomerTier = 'Platinum' AND @OrderTotal >= 500
    BEGIN
        SET @DiscountApplied = 20.00;  -- 20% for Platinum with $500+ orders
    END
    ELSE IF @CustomerTier = 'Platinum'
    BEGIN
        SET @DiscountApplied = 15.00;  -- 15% for Platinum under $500
    END
    ELSE IF @CustomerTier = 'Gold' AND @OrderTotal >= 1000
    BEGIN
        SET @DiscountApplied = 15.00;  -- 15% for Gold with $1000+ orders
    END
    ELSE IF @CustomerTier = 'Gold'
    BEGIN
        SET @DiscountApplied = 10.00;  -- 10% for Gold under $1000
    END
    ELSE IF @CustomerTier = 'Silver'
    BEGIN
        SET @DiscountApplied = 5.00;   -- 5% for Silver
    END
    ELSE
    BEGIN
        SET @DiscountApplied = 0;      -- No discount for others
    END
    
    -- Calculate final total
    SET @DiscountedTotal = @OrderTotal * (1 - @DiscountApplied / 100);
    
    -- Nested IF for additional rules
    IF @DiscountedTotal > 0 AND @DiscountApplied > 0
    BEGIN
        -- Log significant discounts for audit
        IF @DiscountApplied >= 15
        BEGIN
            INSERT INTO Audit.DiscountLog (OrderID, DiscountPercent, LogDate)
            VALUES (@OrderID, @DiscountApplied, GETDATE());
        END
    END
END;
GO
 
-- Note: T-SQL uses BEGIN/END for multi-statement blocks
-- Single statements don't require BEGIN/END:
-- IF @x > 0
--     SELECT 'Positive';  -- Single statement, no BEGIN needed

IF vs CASE

Use IF/ELSE when executing different statements based on conditions. Use CASE when selecting different values within a single expression. IF controls flow; CASE selects values.

CASE Statements

CASE provides conditional logic within expressions. Unlike IF which controls statement execution, CASE evaluates to a value. There are two forms:

Simple CASE:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Searched CASE:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

case_statements.sql

-- CASE in stored procedures
CREATE PROCEDURE Reports.GenerateStatusReport
    @ReportType VARCHAR(20),
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Simple CASE for report type routing
    DECLARE @ReportTitle VARCHAR(100);
    
    SET @ReportTitle = CASE @ReportType
        WHEN 'DAILY' THEN 'Daily Operations Report'
        WHEN 'WEEKLY' THEN 'Weekly Summary Report'
        WHEN 'MONTHLY' THEN 'Monthly Executive Report'
        WHEN 'QUARTERLY' THEN 'Quarterly Business Review'
        ELSE 'Custom Date Range Report'
    END;
    
    -- Searched CASE for status categorization
    SELECT 
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        
        -- Searched CASE for status category
        CASE 
            WHEN o.Status = 'Pending' AND DATEDIFF(DAY, o.OrderDate, GETDATE()) > 7
                THEN 'Overdue'
            WHEN o.Status = 'Pending'
                THEN 'Active'
            WHEN o.Status IN ('Shipped', 'Delivered')
                THEN 'Completed'
            WHEN o.Status IN ('Cancelled', 'Refunded')
                THEN 'Closed'
            ELSE 'Unknown'
        END AS StatusCategory,
        
        -- Searched CASE for priority calculation
        CASE
            WHEN o.TotalAmount >= 10000 THEN 'Critical'
            WHEN o.TotalAmount >= 5000 THEN 'High'
            WHEN o.TotalAmount >= 1000 THEN 'Medium'
            ELSE 'Standard'
        END AS Priority,
        
        -- Nested CASE expressions
        CASE @ReportType
            WHEN 'DAILY' THEN
                CASE WHEN o.OrderDate = CAST(GETDATE() AS DATE)
                     THEN 'Today' ELSE 'Previous' END
            WHEN 'WEEKLY' THEN
                CASE WHEN o.OrderDate >= DATEADD(DAY, -7, GETDATE())
                     THEN 'This Week' ELSE 'Earlier' END
            ELSE 'In Range'
        END AS TimePeriod
        
    FROM Sales.Orders o
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
    
    -- CASE in conditional assignment
    DECLARE @SummaryLevel VARCHAR(20);
    SET @SummaryLevel = CASE 
        WHEN DATEDIFF(DAY, @StartDate, @EndDate) > 90 THEN 'Monthly'
        WHEN DATEDIFF(DAY, @StartDate, @EndDate) > 14 THEN 'Weekly'
        ELSE 'Daily'
    END;
    
    SELECT @ReportTitle AS Title, @SummaryLevel AS Granularity;
END;
GO

WHILE Loops

WHILE loops execute a block of code repeatedly while a condition remains true. They're essential for iterative processing, batch operations, and situations where the number of iterations isn't known in advance.

Basic structure:

WHILE condition
BEGIN
    -- statements
    -- Must include logic to eventually make condition false!
END

while_loops.sql

-- WHILE loop examples
CREATE PROCEDURE DataMaintenance.ArchiveOldRecords
    @DaysToKeep INT = 365,
    @BatchSize INT = 1000,
    @MaxBatches INT = 100,   -- Safety limit
    @RecordsArchived INT OUTPUT,
    @BatchesProcessed INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @CutoffDate DATE = DATEADD(DAY, -@DaysToKeep, GETDATE());
    DECLARE @CurrentBatch INT = 0;
    DECLARE @RowsAffected INT = 1;  -- Initialize to enter loop
    
    SET @RecordsArchived = 0;
    SET @BatchesProcessed = 0;
    
    -- WHILE loop with multiple exit conditions
    WHILE @RowsAffected > 0 AND @CurrentBatch < @MaxBatches
    BEGIN
        SET @CurrentBatch = @CurrentBatch + 1;
        
        BEGIN TRY
            BEGIN TRANSACTION;
            
            -- Archive batch to archive table
            INSERT INTO Archive.TransactionHistory 
                (TransactionID, TransactionDate, Amount, Details, ArchivedDate)
            SELECT TOP (@BatchSize)
                TransactionID, TransactionDate, Amount, Details, GETDATE()
            FROM Operations.TransactionHistory
            WHERE TransactionDate < @CutoffDate
              AND TransactionID NOT IN (
                  SELECT TransactionID FROM Archive.TransactionHistory
              );
            
            SET @RowsAffected = @@ROWCOUNT;
            
            -- Delete from source
            DELETE FROM Operations.TransactionHistory
            WHERE TransactionID IN (
                SELECT TOP (@BatchSize) TransactionID
                FROM Operations.TransactionHistory
                WHERE TransactionDate < @CutoffDate
            );
            
            COMMIT TRANSACTION;
            
            SET @RecordsArchived = @RecordsArchived + @RowsAffected;
            SET @BatchesProcessed = @CurrentBatch;
            
            -- Optional: Small delay to reduce lock contention
            WAITFOR DELAY '00:00:00.100';  -- 100ms
            
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            -- Log error but continue processing
            INSERT INTO Logs.ErrorLog (ErrorMessage, ErrorDate, ProcedureName)
            VALUES (ERROR_MESSAGE(), GETDATE(), 'ArchiveOldRecords');
        END CATCH
    END
    
    -- Final status
    IF @CurrentBatch >= @MaxBatches
    BEGIN
        INSERT INTO Logs.Info (Message, LogDate)
        VALUES ('Archive reached max batches - more records may remain', GETDATE());
    END
END;
GO
 
-- Counter-based WHILE loop
CREATE PROCEDURE Reports.GenerateDailyReportsForRange
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @CurrentDate DATE = @StartDate;
    DECLARE @DaysProcessed INT = 0;
    
    -- Loop through each day
    WHILE @CurrentDate <= @EndDate
    BEGIN
        -- Generate report for current date
        INSERT INTO Reports.DailySummary (ReportDate, TotalOrders, TotalRevenue, GeneratedAt)
        SELECT 
            @CurrentDate,
            COUNT(*),
            SUM(TotalAmount),
            GETDATE()
        FROM Sales.Orders
        WHERE CAST(OrderDate AS DATE) = @CurrentDate;
        
        -- Move to next day
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
        SET @DaysProcessed = @DaysProcessed + 1;
    END
    
    SELECT @DaysProcessed AS DaysProcessed;
END;
GO

Infinite Loop Protection

Always ensure loop exit conditions will eventually be met. Include iteration counters as safety limits, timeout checks for time-based loops, and proper handling of empty result sets. An infinite loop can lock tables and crash systems.

FOR Loops and Cursors

FOR loops (in PostgreSQL and Oracle) and cursors (all databases) enable row-by-row processing of query results. While set-based operations are generally preferred, cursors are necessary when:

Processing requires different logic for each row
Each row's processing depends on results from previous rows
You need to call external APIs or procedures for each row
Complex business logic can't be expressed in SQL

for_loops_cursors.sql

-- Explicit cursor in SQL Server
CREATE PROCEDURE Notifications.SendOrderConfirmations
    @BatchSize INT = 100,
    @SentCount INT OUTPUT,
    @FailedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Cursor variables
    DECLARE @OrderID INT;
    DECLARE @CustomerEmail VARCHAR(255);
    DECLARE @CustomerName VARCHAR(200);
    DECLARE @OrderTotal DECIMAL(18,2);
    
    SET @SentCount = 0;
    SET @FailedCount = 0;
    
    -- Declare cursor
    DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT TOP (@BatchSize)
            o.OrderID,
            c.Email,
            c.FirstName + ' ' + c.LastName,
            o.TotalAmount
        FROM Sales.Orders o
        JOIN Customers c ON o.CustomerID = c.CustomerID
        WHERE o.ConfirmationSent = 0
          AND o.Status = 'Confirmed'
        ORDER BY o.OrderDate;
    
    -- Open cursor
    OPEN order_cursor;
    
    -- Fetch first row
    FETCH NEXT FROM order_cursor 
    INTO @OrderID, @CustomerEmail, @CustomerName, @OrderTotal;
    
    -- Process rows
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- Call email procedure for each order
            EXEC Communications.SendEmail
                @ToAddress = @CustomerEmail,
                @Subject = 'Order Confirmation',
                @TemplateName = 'OrderConfirmation',
                @TemplateData = @OrderID;
            
            -- Mark as sent
            UPDATE Sales.Orders
            SET ConfirmationSent = 1, ConfirmationDate = GETDATE()
            WHERE OrderID = @OrderID;
            
            SET @SentCount = @SentCount + 1;
            
        END TRY
        BEGIN CATCH
            -- Log failure
            INSERT INTO Logs.EmailFailures (OrderID, Email, Error, AttemptDate)
            VALUES (@OrderID, @CustomerEmail, ERROR_MESSAGE(), GETDATE());
            
            SET @FailedCount = @FailedCount + 1;
        END CATCH
        
        -- Fetch next row
        FETCH NEXT FROM order_cursor 
        INTO @OrderID, @CustomerEmail, @CustomerName, @OrderTotal;
    END
    
    -- Cleanup
    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END;
GO
 
-- Cursor with UPDATE WHERE CURRENT OF
CREATE PROCEDURE Inventory.ApplyPriceAdjustments
    @AdjustmentType VARCHAR(20),
    @AdjustmentPercent DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ProductID INT;
    DECLARE @CurrentPrice DECIMAL(18,2);
    DECLARE @NewPrice DECIMAL(18,2);
    
    -- Cursor for update
    DECLARE price_cursor CURSOR LOCAL FOR
        SELECT ProductID, UnitPrice
        FROM Products
        WHERE Category = @AdjustmentType
          AND Active = 1
        FOR UPDATE OF UnitPrice;  -- Lock for update
    
    OPEN price_cursor;
    FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Calculate new price with business rules
        SET @NewPrice = @CurrentPrice * (1 + @AdjustmentPercent / 100);
        
        -- Ensure minimum price
        IF @NewPrice < 0.99
            SET @NewPrice = 0.99;
        
        -- Round to nearest 0.99
        SET @NewPrice = ROUND(@NewPrice, 0) - 0.01;
        
        -- Update using cursor position
        UPDATE Products
        SET UnitPrice = @NewPrice,
            LastPriceUpdate = GETDATE()
        WHERE CURRENT OF price_cursor;
        
        -- Log change
        INSERT INTO Audit.PriceChanges (ProductID, OldPrice, NewPrice, ChangeDate)
        VALUES (@ProductID, @CurrentPrice, @NewPrice, GETDATE());
        
        FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice;
    END
    
    CLOSE price_cursor;
    DEALLOCATE price_cursor;
END;
GO

Cursors: Use Sparingly

Cursors process row-by-row, which is inherently slower than set-based operations. A single UPDATE with WHERE clause is almost always faster than a cursor that updates each row. Use cursors only when set-based logic truly cannot express your requirements.

BREAK, CONTINUE, and RETURN

Control transfer statements alter the normal flow of execution by jumping out of loops or procedures entirely:

BREAK (LEAVE/EXIT) — Immediately exits the innermost loop
CONTINUE (ITERATE) — Skips to the next iteration of the loop
RETURN — Exits the procedure immediately, optionally with a value

Control Transfer Statement Syntax by Database
Action	SQL Server	PostgreSQL	MySQL	Oracle
Exit loop	BREAK	EXIT	LEAVE loop_label	EXIT
Skip iteration	CONTINUE	CONTINUE	ITERATE loop_label	CONTINUE
Exit procedure	RETURN	RETURN	LEAVE (or just end)	RETURN
Return value	RETURN value	N/A (use INOUT)	N/A	N/A (procedures)

control_transfer.sql

-- BREAK, CONTINUE, and RETURN examples
CREATE PROCEDURE Processing.ProcessWithControlFlow
    @MaxItems INT,
    @SkipPriority VARCHAR(20) = NULL,
    @StopOnFirstError BIT = 1,
    @ProcessedCount INT OUTPUT,
    @SkippedCount INT OUTPUT,
    @ErrorCount INT OUTPUT,
    @ReturnCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ItemID INT;
    DECLARE @Priority VARCHAR(20);
    DECLARE @ItemCount INT = 0;
    
    SET @ProcessedCount = 0;
    SET @SkippedCount = 0;
    SET @ErrorCount = 0;
    SET @ReturnCode = 0;
    
    -- Cursor for items
    DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT ItemID, Priority
        FROM Processing.Items
        WHERE Status = 'Pending'
        ORDER BY Priority DESC;
    
    OPEN item_cursor;
    FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ItemCount = @ItemCount + 1;
        
        -- BREAK: Exit loop if max reached
        IF @ItemCount > @MaxItems
        BEGIN
            SET @ReturnCode = 1;  -- Partial completion
            BREAK;  -- Exit the WHILE loop
        END
        
        -- CONTINUE: Skip items with specified priority
        IF @Priority = @SkipPriority
        BEGIN
            SET @SkippedCount = @SkippedCount + 1;
            FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
            CONTINUE;  -- Skip to next iteration
        END
        
        -- Process the item
        BEGIN TRY
            EXEC Processing.ProcessSingleItem @ItemID;
            SET @ProcessedCount = @ProcessedCount + 1;
        END TRY
        BEGIN CATCH
            SET @ErrorCount = @ErrorCount + 1;
            
            -- BREAK on error if configured
            IF @StopOnFirstError = 1
            BEGIN
                SET @ReturnCode = -1;  -- Error exit
                BREAK;
            END
            -- Otherwise CONTINUE to next item (implicit)
        END CATCH
        
        FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
    END
    
    CLOSE item_cursor;
    DEALLOCATE item_cursor;
    
    -- RETURN with status code
    IF @ErrorCount > 0 AND @ReturnCode = 0
        SET @ReturnCode = 2;  -- Completed with errors
    
    RETURN @ReturnCode;  -- Exit procedure with value
END;
GO
 
-- Calling and handling return code
DECLARE @Processed INT, @Skipped INT, @Errors INT, @Result INT;
 
EXEC @Result = Processing.ProcessWithControlFlow
    @MaxItems = 50,
    @SkipPriority = 'Low',
    @StopOnFirstError = 0,
    @ProcessedCount = @Processed OUTPUT,
    @SkippedCount = @Skipped OUTPUT,
    @ErrorCount = @Errors OUTPUT,
    @ReturnCode = @Result OUTPUT;
 
SELECT 
    @Result AS ReturnCode,
    CASE @Result
        WHEN 0 THEN 'Success'
        WHEN 1 THEN 'Partial - reached max'
        WHEN 2 THEN 'Completed with errors'
        WHEN -1 THEN 'Stopped on error'
    END AS Status,
    @Processed AS Processed,
    @Skipped AS Skipped,
    @Errors AS Errors;

Control Flow Best Practices

Effective control flow makes procedures readable, maintainable, and performant. These best practices help avoid common pitfalls:

Control Flow Best Practices

•Prefer set-based operations — A single UPDATE with WHERE is faster than any loop. Use cursors only when row-by-row logic is truly necessary.
•Use early returns — Exit early when validation fails rather than deeply nesting the happy path. IF invalid RETURN is cleaner than IF valid BEGIN ... long code ... END.
•Limit loop iterations — Always include maximum iteration checks to prevent infinite loops. Better to exit early than hang forever.
•Avoid deep nesting — More than 3-4 levels of nested IF/WHILE becomes hard to follow. Refactor into separate procedures or use early returns.
•Name complex loops — In PostgreSQL/MySQL, use labeled loops (<<loop_name>>) for clarity when nesting or using EXIT/CONTINUE.
•Use CASE for value selection — Don't use IF/ELSE to set a variable to different values; use CASE expression instead.
•Handle the ELSE case — Always include ELSE in IF chains and CASE expressions to handle unexpected values explicitly.
•Batch operations in loops — When looping over large datasets, process in batches with periodic COMMIT to avoid transaction log bloat.

bad_pattern.sql

Anti-Pattern

-- ❌ BAD: Deep nesting, no early return
IF @CustomerID IS NOT NULL
BEGIN
    IF EXISTS (SELECT 1 FROM Customers 
               WHERE CustomerID = @CustomerID)
    BEGIN
        IF @Amount > 0
        BEGIN
            IF @Amount <= @Balance
            BEGIN
                -- Finally, the actual logic
                -- buried in 4 levels of nesting
                UPDATE Accounts
                SET Balance = Balance - @Amount
                WHERE CustomerID = @CustomerID;
            END
            ELSE
            BEGIN
                SET @Error = 'Insufficient funds';
            END
        END
        ELSE
        BEGIN
            SET @Error = 'Invalid amount';
        END
    END
    ELSE
    BEGIN
        SET @Error = 'Customer not found';
    END
END
ELSE
BEGIN
    SET @Error = 'Customer ID required';
END

good_pattern.sql

Best Practice

-- ✅ GOOD: Early returns, flat structure
IF @CustomerID IS NULL
BEGIN
    SET @Error = 'Customer ID required';
    RETURN;
END
 
IF NOT EXISTS (SELECT 1 FROM Customers 
               WHERE CustomerID = @CustomerID)
BEGIN
    SET @Error = 'Customer not found';
    RETURN;
END
 
IF @Amount <= 0
BEGIN
    SET @Error = 'Invalid amount';
    RETURN;
END
 
IF @Amount > @Balance
BEGIN
    SET @Error = 'Insufficient funds';
    RETURN;
END
 
-- Happy path: clear and unindented
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE CustomerID = @CustomerID;
 
SET @Error = NULL;  -- Success

Summary: Mastering Procedural Control

We've covered the essential control flow constructs for stored procedures. Here are the key takeaways:

Key Takeaways

•IF/ELSE enables conditional execution — Run different code paths based on conditions. Avoid deep nesting with early returns.
•CASE selects values conditionally — Simple CASE compares values; searched CASE evaluates conditions. Use in expressions.
•WHILE loops repeat until condition is false — Essential for batch processing. Always include exit conditions.
•FOR loops and cursors process rows — Use when set-based operations can't express the logic. Consider performance impact.
•BREAK/EXIT leaves loops immediately — Use for early termination on errors or completion.
•CONTINUE/ITERATE skips to next iteration — Use to bypass specific cases without exiting the loop.
•RETURN exits procedures — Can return status codes. Use for early exit on errors.
•Prefer set-based over procedural — Loops are powerful but slower. Always consider if a single SQL statement can do the job.

What's next:

Control flow handles the happy path, but real-world procedures must handle errors gracefully. The next page covers error handling—TRY/CATCH, exception handlers, and patterns for robust procedure development.

Page Complete

You now understand how to control execution flow in stored procedures using conditionals, loops, and control transfer statements. Next, we'll learn to handle errors and exceptions for production-ready procedures.

Control Flow

From Declarative to Procedural

What You Will Learn

IF/ELSE Statements

IF/ELSE statements enable conditional execution—running different code based on whether a condition is true or false. This is the fundamental decision-making construct in procedural SQL.

Structure:

IF condition THEN — Executes if condition is TRUE
ELSEIF/ELSIF condition THEN — Additional conditions (optional)
ELSE — Executes if no conditions matched (optional)
END IF — Terminates the IF block

if_else.sql

-- Basic IF/ELSE syntax in T-SQL
CREATE PROCEDURE Pricing.ApplyDiscount
    @OrderID INT,
    @CustomerTier VARCHAR(20),
    @OrderTotal DECIMAL(18,2),
    @DiscountedTotal DECIMAL(18,2) OUTPUT,
    @DiscountApplied DECIMAL(5,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Simple IF
    IF @OrderTotal <= 0
    BEGIN
        SET @DiscountedTotal = 0;
        SET @DiscountApplied = 0;
        RETURN;
    END
    
    -- IF/ELSE IF/ELSE chain
    IF @CustomerTier = 'Platinum' AND @OrderTotal >= 500
    BEGIN
        SET @DiscountApplied = 20.00;  -- 20% for Platinum with $500+ orders
    END
    ELSE IF @CustomerTier = 'Platinum'
    BEGIN
        SET @DiscountApplied = 15.00;  -- 15% for Platinum under $500
    END
    ELSE IF @CustomerTier = 'Gold' AND @OrderTotal >= 1000
    BEGIN
        SET @DiscountApplied = 15.00;  -- 15% for Gold with $1000+ orders
    END
    ELSE IF @CustomerTier = 'Gold'
    BEGIN
        SET @DiscountApplied = 10.00;  -- 10% for Gold under $1000
    END
    ELSE IF @CustomerTier = 'Silver'
    BEGIN
        SET @DiscountApplied = 5.00;   -- 5% for Silver
    END
    ELSE
    BEGIN
        SET @DiscountApplied = 0;      -- No discount for others
    END
    
    -- Calculate final total
    SET @DiscountedTotal = @OrderTotal * (1 - @DiscountApplied / 100);
    
    -- Nested IF for additional rules
    IF @DiscountedTotal > 0 AND @DiscountApplied > 0
    BEGIN
        -- Log significant discounts for audit
        IF @DiscountApplied >= 15
        BEGIN
            INSERT INTO Audit.DiscountLog (OrderID, DiscountPercent, LogDate)
            VALUES (@OrderID, @DiscountApplied, GETDATE());
        END
    END
END;
GO
 
-- Note: T-SQL uses BEGIN/END for multi-statement blocks
-- Single statements don't require BEGIN/END:
-- IF @x > 0
--     SELECT 'Positive';  -- Single statement, no BEGIN needed

IF vs CASE

Use IF/ELSE when executing different statements based on conditions. Use CASE when selecting different values within a single expression. IF controls flow; CASE selects values.

CASE Statements

CASE provides conditional logic within expressions. Unlike IF which controls statement execution, CASE evaluates to a value. There are two forms:

Simple CASE:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Searched CASE:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

case_statements.sql

-- CASE in stored procedures
CREATE PROCEDURE Reports.GenerateStatusReport
    @ReportType VARCHAR(20),
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Simple CASE for report type routing
    DECLARE @ReportTitle VARCHAR(100);
    
    SET @ReportTitle = CASE @ReportType
        WHEN 'DAILY' THEN 'Daily Operations Report'
        WHEN 'WEEKLY' THEN 'Weekly Summary Report'
        WHEN 'MONTHLY' THEN 'Monthly Executive Report'
        WHEN 'QUARTERLY' THEN 'Quarterly Business Review'
        ELSE 'Custom Date Range Report'
    END;
    
    -- Searched CASE for status categorization
    SELECT 
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        
        -- Searched CASE for status category
        CASE 
            WHEN o.Status = 'Pending' AND DATEDIFF(DAY, o.OrderDate, GETDATE()) > 7
                THEN 'Overdue'
            WHEN o.Status = 'Pending'
                THEN 'Active'
            WHEN o.Status IN ('Shipped', 'Delivered')
                THEN 'Completed'
            WHEN o.Status IN ('Cancelled', 'Refunded')
                THEN 'Closed'
            ELSE 'Unknown'
        END AS StatusCategory,
        
        -- Searched CASE for priority calculation
        CASE
            WHEN o.TotalAmount >= 10000 THEN 'Critical'
            WHEN o.TotalAmount >= 5000 THEN 'High'
            WHEN o.TotalAmount >= 1000 THEN 'Medium'
            ELSE 'Standard'
        END AS Priority,
        
        -- Nested CASE expressions
        CASE @ReportType
            WHEN 'DAILY' THEN
                CASE WHEN o.OrderDate = CAST(GETDATE() AS DATE)
                     THEN 'Today' ELSE 'Previous' END
            WHEN 'WEEKLY' THEN
                CASE WHEN o.OrderDate >= DATEADD(DAY, -7, GETDATE())
                     THEN 'This Week' ELSE 'Earlier' END
            ELSE 'In Range'
        END AS TimePeriod
        
    FROM Sales.Orders o
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
    
    -- CASE in conditional assignment
    DECLARE @SummaryLevel VARCHAR(20);
    SET @SummaryLevel = CASE 
        WHEN DATEDIFF(DAY, @StartDate, @EndDate) > 90 THEN 'Monthly'
        WHEN DATEDIFF(DAY, @StartDate, @EndDate) > 14 THEN 'Weekly'
        ELSE 'Daily'
    END;
    
    SELECT @ReportTitle AS Title, @SummaryLevel AS Granularity;
END;
GO

WHILE Loops

Basic structure:

WHILE condition
BEGIN
    -- statements
    -- Must include logic to eventually make condition false!
END

while_loops.sql

-- WHILE loop examples
CREATE PROCEDURE DataMaintenance.ArchiveOldRecords
    @DaysToKeep INT = 365,
    @BatchSize INT = 1000,
    @MaxBatches INT = 100,   -- Safety limit
    @RecordsArchived INT OUTPUT,
    @BatchesProcessed INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @CutoffDate DATE = DATEADD(DAY, -@DaysToKeep, GETDATE());
    DECLARE @CurrentBatch INT = 0;
    DECLARE @RowsAffected INT = 1;  -- Initialize to enter loop
    
    SET @RecordsArchived = 0;
    SET @BatchesProcessed = 0;
    
    -- WHILE loop with multiple exit conditions
    WHILE @RowsAffected > 0 AND @CurrentBatch < @MaxBatches
    BEGIN
        SET @CurrentBatch = @CurrentBatch + 1;
        
        BEGIN TRY
            BEGIN TRANSACTION;
            
            -- Archive batch to archive table
            INSERT INTO Archive.TransactionHistory 
                (TransactionID, TransactionDate, Amount, Details, ArchivedDate)
            SELECT TOP (@BatchSize)
                TransactionID, TransactionDate, Amount, Details, GETDATE()
            FROM Operations.TransactionHistory
            WHERE TransactionDate < @CutoffDate
              AND TransactionID NOT IN (
                  SELECT TransactionID FROM Archive.TransactionHistory
              );
            
            SET @RowsAffected = @@ROWCOUNT;
            
            -- Delete from source
            DELETE FROM Operations.TransactionHistory
            WHERE TransactionID IN (
                SELECT TOP (@BatchSize) TransactionID
                FROM Operations.TransactionHistory
                WHERE TransactionDate < @CutoffDate
            );
            
            COMMIT TRANSACTION;
            
            SET @RecordsArchived = @RecordsArchived + @RowsAffected;
            SET @BatchesProcessed = @CurrentBatch;
            
            -- Optional: Small delay to reduce lock contention
            WAITFOR DELAY '00:00:00.100';  -- 100ms
            
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            -- Log error but continue processing
            INSERT INTO Logs.ErrorLog (ErrorMessage, ErrorDate, ProcedureName)
            VALUES (ERROR_MESSAGE(), GETDATE(), 'ArchiveOldRecords');
        END CATCH
    END
    
    -- Final status
    IF @CurrentBatch >= @MaxBatches
    BEGIN
        INSERT INTO Logs.Info (Message, LogDate)
        VALUES ('Archive reached max batches - more records may remain', GETDATE());
    END
END;
GO
 
-- Counter-based WHILE loop
CREATE PROCEDURE Reports.GenerateDailyReportsForRange
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @CurrentDate DATE = @StartDate;
    DECLARE @DaysProcessed INT = 0;
    
    -- Loop through each day
    WHILE @CurrentDate <= @EndDate
    BEGIN
        -- Generate report for current date
        INSERT INTO Reports.DailySummary (ReportDate, TotalOrders, TotalRevenue, GeneratedAt)
        SELECT 
            @CurrentDate,
            COUNT(*),
            SUM(TotalAmount),
            GETDATE()
        FROM Sales.Orders
        WHERE CAST(OrderDate AS DATE) = @CurrentDate;
        
        -- Move to next day
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
        SET @DaysProcessed = @DaysProcessed + 1;
    END
    
    SELECT @DaysProcessed AS DaysProcessed;
END;
GO

Infinite Loop Protection

FOR Loops and Cursors

FOR loops (in PostgreSQL and Oracle) and cursors (all databases) enable row-by-row processing of query results. While set-based operations are generally preferred, cursors are necessary when:

Processing requires different logic for each row
Each row's processing depends on results from previous rows
You need to call external APIs or procedures for each row
Complex business logic can't be expressed in SQL

for_loops_cursors.sql

-- Explicit cursor in SQL Server
CREATE PROCEDURE Notifications.SendOrderConfirmations
    @BatchSize INT = 100,
    @SentCount INT OUTPUT,
    @FailedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Cursor variables
    DECLARE @OrderID INT;
    DECLARE @CustomerEmail VARCHAR(255);
    DECLARE @CustomerName VARCHAR(200);
    DECLARE @OrderTotal DECIMAL(18,2);
    
    SET @SentCount = 0;
    SET @FailedCount = 0;
    
    -- Declare cursor
    DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT TOP (@BatchSize)
            o.OrderID,
            c.Email,
            c.FirstName + ' ' + c.LastName,
            o.TotalAmount
        FROM Sales.Orders o
        JOIN Customers c ON o.CustomerID = c.CustomerID
        WHERE o.ConfirmationSent = 0
          AND o.Status = 'Confirmed'
        ORDER BY o.OrderDate;
    
    -- Open cursor
    OPEN order_cursor;
    
    -- Fetch first row
    FETCH NEXT FROM order_cursor 
    INTO @OrderID, @CustomerEmail, @CustomerName, @OrderTotal;
    
    -- Process rows
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- Call email procedure for each order
            EXEC Communications.SendEmail
                @ToAddress = @CustomerEmail,
                @Subject = 'Order Confirmation',
                @TemplateName = 'OrderConfirmation',
                @TemplateData = @OrderID;
            
            -- Mark as sent
            UPDATE Sales.Orders
            SET ConfirmationSent = 1, ConfirmationDate = GETDATE()
            WHERE OrderID = @OrderID;
            
            SET @SentCount = @SentCount + 1;
            
        END TRY
        BEGIN CATCH
            -- Log failure
            INSERT INTO Logs.EmailFailures (OrderID, Email, Error, AttemptDate)
            VALUES (@OrderID, @CustomerEmail, ERROR_MESSAGE(), GETDATE());
            
            SET @FailedCount = @FailedCount + 1;
        END CATCH
        
        -- Fetch next row
        FETCH NEXT FROM order_cursor 
        INTO @OrderID, @CustomerEmail, @CustomerName, @OrderTotal;
    END
    
    -- Cleanup
    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END;
GO
 
-- Cursor with UPDATE WHERE CURRENT OF
CREATE PROCEDURE Inventory.ApplyPriceAdjustments
    @AdjustmentType VARCHAR(20),
    @AdjustmentPercent DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ProductID INT;
    DECLARE @CurrentPrice DECIMAL(18,2);
    DECLARE @NewPrice DECIMAL(18,2);
    
    -- Cursor for update
    DECLARE price_cursor CURSOR LOCAL FOR
        SELECT ProductID, UnitPrice
        FROM Products
        WHERE Category = @AdjustmentType
          AND Active = 1
        FOR UPDATE OF UnitPrice;  -- Lock for update
    
    OPEN price_cursor;
    FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Calculate new price with business rules
        SET @NewPrice = @CurrentPrice * (1 + @AdjustmentPercent / 100);
        
        -- Ensure minimum price
        IF @NewPrice < 0.99
            SET @NewPrice = 0.99;
        
        -- Round to nearest 0.99
        SET @NewPrice = ROUND(@NewPrice, 0) - 0.01;
        
        -- Update using cursor position
        UPDATE Products
        SET UnitPrice = @NewPrice,
            LastPriceUpdate = GETDATE()
        WHERE CURRENT OF price_cursor;
        
        -- Log change
        INSERT INTO Audit.PriceChanges (ProductID, OldPrice, NewPrice, ChangeDate)
        VALUES (@ProductID, @CurrentPrice, @NewPrice, GETDATE());
        
        FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice;
    END
    
    CLOSE price_cursor;
    DEALLOCATE price_cursor;
END;
GO

Cursors: Use Sparingly

BREAK, CONTINUE, and RETURN

Control transfer statements alter the normal flow of execution by jumping out of loops or procedures entirely:

BREAK (LEAVE/EXIT) — Immediately exits the innermost loop
CONTINUE (ITERATE) — Skips to the next iteration of the loop
RETURN — Exits the procedure immediately, optionally with a value

Control Transfer Statement Syntax by Database
Action	SQL Server	PostgreSQL	MySQL	Oracle
Exit loop	BREAK	EXIT	LEAVE loop_label	EXIT
Skip iteration	CONTINUE	CONTINUE	ITERATE loop_label	CONTINUE
Exit procedure	RETURN	RETURN	LEAVE (or just end)	RETURN
Return value	RETURN value	N/A (use INOUT)	N/A	N/A (procedures)

control_transfer.sql

-- BREAK, CONTINUE, and RETURN examples
CREATE PROCEDURE Processing.ProcessWithControlFlow
    @MaxItems INT,
    @SkipPriority VARCHAR(20) = NULL,
    @StopOnFirstError BIT = 1,
    @ProcessedCount INT OUTPUT,
    @SkippedCount INT OUTPUT,
    @ErrorCount INT OUTPUT,
    @ReturnCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ItemID INT;
    DECLARE @Priority VARCHAR(20);
    DECLARE @ItemCount INT = 0;
    
    SET @ProcessedCount = 0;
    SET @SkippedCount = 0;
    SET @ErrorCount = 0;
    SET @ReturnCode = 0;
    
    -- Cursor for items
    DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT ItemID, Priority
        FROM Processing.Items
        WHERE Status = 'Pending'
        ORDER BY Priority DESC;
    
    OPEN item_cursor;
    FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ItemCount = @ItemCount + 1;
        
        -- BREAK: Exit loop if max reached
        IF @ItemCount > @MaxItems
        BEGIN
            SET @ReturnCode = 1;  -- Partial completion
            BREAK;  -- Exit the WHILE loop
        END
        
        -- CONTINUE: Skip items with specified priority
        IF @Priority = @SkipPriority
        BEGIN
            SET @SkippedCount = @SkippedCount + 1;
            FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
            CONTINUE;  -- Skip to next iteration
        END
        
        -- Process the item
        BEGIN TRY
            EXEC Processing.ProcessSingleItem @ItemID;
            SET @ProcessedCount = @ProcessedCount + 1;
        END TRY
        BEGIN CATCH
            SET @ErrorCount = @ErrorCount + 1;
            
            -- BREAK on error if configured
            IF @StopOnFirstError = 1
            BEGIN
                SET @ReturnCode = -1;  -- Error exit
                BREAK;
            END
            -- Otherwise CONTINUE to next item (implicit)
        END CATCH
        
        FETCH NEXT FROM item_cursor INTO @ItemID, @Priority;
    END
    
    CLOSE item_cursor;
    DEALLOCATE item_cursor;
    
    -- RETURN with status code
    IF @ErrorCount > 0 AND @ReturnCode = 0
        SET @ReturnCode = 2;  -- Completed with errors
    
    RETURN @ReturnCode;  -- Exit procedure with value
END;
GO
 
-- Calling and handling return code
DECLARE @Processed INT, @Skipped INT, @Errors INT, @Result INT;
 
EXEC @Result = Processing.ProcessWithControlFlow
    @MaxItems = 50,
    @SkipPriority = 'Low',
    @StopOnFirstError = 0,
    @ProcessedCount = @Processed OUTPUT,
    @SkippedCount = @Skipped OUTPUT,
    @ErrorCount = @Errors OUTPUT,
    @ReturnCode = @Result OUTPUT;
 
SELECT 
    @Result AS ReturnCode,
    CASE @Result
        WHEN 0 THEN 'Success'
        WHEN 1 THEN 'Partial - reached max'
        WHEN 2 THEN 'Completed with errors'
        WHEN -1 THEN 'Stopped on error'
    END AS Status,
    @Processed AS Processed,
    @Skipped AS Skipped,
    @Errors AS Errors;

Control Flow Best Practices

Effective control flow makes procedures readable, maintainable, and performant. These best practices help avoid common pitfalls:

Control Flow Best Practices

•Prefer set-based operations — A single UPDATE with WHERE is faster than any loop. Use cursors only when row-by-row logic is truly necessary.
•Use early returns — Exit early when validation fails rather than deeply nesting the happy path. IF invalid RETURN is cleaner than IF valid BEGIN ... long code ... END.
•Limit loop iterations — Always include maximum iteration checks to prevent infinite loops. Better to exit early than hang forever.
•Avoid deep nesting — More than 3-4 levels of nested IF/WHILE becomes hard to follow. Refactor into separate procedures or use early returns.
•Name complex loops — In PostgreSQL/MySQL, use labeled loops (<<loop_name>>) for clarity when nesting or using EXIT/CONTINUE.
•Use CASE for value selection — Don't use IF/ELSE to set a variable to different values; use CASE expression instead.
•Handle the ELSE case — Always include ELSE in IF chains and CASE expressions to handle unexpected values explicitly.
•Batch operations in loops — When looping over large datasets, process in batches with periodic COMMIT to avoid transaction log bloat.

bad_pattern.sql

Anti-Pattern

-- ❌ BAD: Deep nesting, no early return
IF @CustomerID IS NOT NULL
BEGIN
    IF EXISTS (SELECT 1 FROM Customers 
               WHERE CustomerID = @CustomerID)
    BEGIN
        IF @Amount > 0
        BEGIN
            IF @Amount <= @Balance
            BEGIN
                -- Finally, the actual logic
                -- buried in 4 levels of nesting
                UPDATE Accounts
                SET Balance = Balance - @Amount
                WHERE CustomerID = @CustomerID;
            END
            ELSE
            BEGIN
                SET @Error = 'Insufficient funds';
            END
        END
        ELSE
        BEGIN
            SET @Error = 'Invalid amount';
        END
    END
    ELSE
    BEGIN
        SET @Error = 'Customer not found';
    END
END
ELSE
BEGIN
    SET @Error = 'Customer ID required';
END

good_pattern.sql

Best Practice

-- ✅ GOOD: Early returns, flat structure
IF @CustomerID IS NULL
BEGIN
    SET @Error = 'Customer ID required';
    RETURN;
END
 
IF NOT EXISTS (SELECT 1 FROM Customers 
               WHERE CustomerID = @CustomerID)
BEGIN
    SET @Error = 'Customer not found';
    RETURN;
END
 
IF @Amount <= 0
BEGIN
    SET @Error = 'Invalid amount';
    RETURN;
END
 
IF @Amount > @Balance
BEGIN
    SET @Error = 'Insufficient funds';
    RETURN;
END
 
-- Happy path: clear and unindented
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE CustomerID = @CustomerID;
 
SET @Error = NULL;  -- Success

Summary: Mastering Procedural Control

We've covered the essential control flow constructs for stored procedures. Here are the key takeaways:

Key Takeaways

•IF/ELSE enables conditional execution — Run different code paths based on conditions. Avoid deep nesting with early returns.
•CASE selects values conditionally — Simple CASE compares values; searched CASE evaluates conditions. Use in expressions.
•WHILE loops repeat until condition is false — Essential for batch processing. Always include exit conditions.
•FOR loops and cursors process rows — Use when set-based operations can't express the logic. Consider performance impact.
•BREAK/EXIT leaves loops immediately — Use for early termination on errors or completion.
•CONTINUE/ITERATE skips to next iteration — Use to bypass specific cases without exiting the loop.
•RETURN exits procedures — Can return status codes. Use for early exit on errors.
•Prefer set-based over procedural — Loops are powerful but slower. Always consider if a single SQL statement can do the job.

What's next:

Page Complete