T-SQL Control Flow Language

The Transact-SQL (T-SQL) control flow language allows you to control the execution path of your SQL statements. This includes making decisions, repeating actions, and managing the flow of execution within a batch or stored procedure.

Conditional Statements

Conditional statements allow you to execute different blocks of code based on whether a certain condition is true or false.

IF...ELSE Statement

The IF...ELSE statement evaluates a Boolean expression and executes one block of statements if the expression is true, and optionally another block if the expression is false.

IF condition_expression
BEGIN
    -- SQL statements to execute if condition is TRUE
END
ELSE
BEGIN
    -- SQL statements to execute if condition is FALSE
END

CASE Expression

The CASE expression allows you to return one of several possible result expressions based on the evaluation of a condition. It can be used within SQL statements.

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

Looping Statements

Looping statements allow you to repeatedly execute a block of T-SQL statements.

WHILE Loop

The WHILE loop executes a set of statements as long as a specified Boolean condition is true.

WHILE condition_expression
BEGIN
    -- SQL statements to execute repeatedly
    -- ...
    -- Ensure condition will eventually become FALSE to avoid infinite loop
END

BREAK and CONTINUE

BREAK exits the innermost WHILE loop immediately. CONTINUE restarts the WHILE loop, skipping any remaining statements in the current iteration.

Control Flow Statements

These statements alter the normal flow of execution.

GOTO Statement

The GOTO statement transfers control to another statement within the same batch or stored procedure. It should be used with caution as it can lead to less readable code.

-- ...
IF some_condition GOTO LabelName
-- ...
LabelName:
    -- Statements following the label

WAITFOR Statement

The WAITFOR statement suspends the execution of a batch or stored procedure until the specified time or for the specified duration.

-- Wait until a specific time
WAITFOR TIME '23:59:59'

-- Wait for a duration
WAITFOR DELAY '00:01:00'
Developer Tip:

When using control flow statements, always consider edge cases and potential infinite loops. Structured code with clear logic is more maintainable and less prone to errors.

Control Flow within Triggers and Stored Procedures

Control flow language is extensively used within stored procedures and triggers to implement complex business logic, data validation, and error handling.

Example: Basic Stored Procedure with IF ELSE

CREATE PROCEDURE GetProductStatus
    @ProductID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StockLevel INT;

    SELECT @StockLevel = StockQuantity
    FROM Products
    WHERE ProductID = @ProductID;

    IF @StockLevel > 50
    BEGIN
        PRINT 'Product is in stock.';
    END
    ELSE IF @StockLevel > 0
    BEGIN
        PRINT 'Product stock is low.';
    END
    ELSE
    BEGIN
        PRINT 'Product is out of stock.';
    END
END

Example: WHILE loop for data processing

DECLARE @Counter INT = 1;
DECLARE @MaxCount INT;

SELECT @MaxCount = COUNT(*) FROM Orders;

WHILE @Counter <= @MaxCount
BEGIN
    -- Process order logic here using @Counter
    PRINT 'Processing Order Number: ' + CAST(@Counter AS VARCHAR);

    SET @Counter = @Counter + 1;
END