T-SQL Control Flow Statements

Control flow statements are essential for directing the execution path of your T-SQL code. They allow you to make decisions, repeat actions, and manage the logic of your stored procedures, functions, and scripts.

IF...ELSE Statement

The IF...ELSE statement allows you to execute a block of code conditionally based on whether a specified condition evaluates to true or false.

Example: Simple IF


IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTable')
BEGIN
    PRINT 'MyTable already exists.';
END
ELSE
BEGIN
    PRINT 'MyTable does not exist.';
END

Example: IF with ELSE IF


DECLARE @Score INT = 75;

IF @Score >= 90
BEGIN
    PRINT 'Grade: A';
END
ELSE IF @Score >= 80
BEGIN
    PRINT 'Grade: B';
END
ELSE IF @Score >= 70
BEGIN
    PRINT 'Grade: C';
END
ELSE
BEGIN
    PRINT 'Grade: D';
END

WHILE Loop

The WHILE loop repeatedly executes a block of Transact-SQL statements as long as a specified condition remains true.

Example: Counting with WHILE


DECLARE @Counter INT = 1;

WHILE @Counter <= 5
BEGIN
    PRINT 'Count: ' + CAST(@Counter AS VARCHAR(10));
    SET @Counter = @Counter + 1;
END

BEGIN...END Blocks

BEGIN...END blocks are used to group one or more Transact-SQL statements into a single logical unit. This is particularly useful within control flow statements like IF and WHILE when you need to execute multiple statements.

Example: Grouping Statements


IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyFunction]') AND type IN (N'FN', N'IF', N'TF'))
BEGIN
    PRINT 'Function exists.';
    -- Additional logic here
    DROP FUNCTION [dbo].[MyFunction];
    PRINT 'Function dropped.';
END
ELSE
BEGIN
    PRINT 'Function does not exist.';
END

CASE Expression

The CASE expression allows you to perform conditional logic within a query or statement. It returns one of several possible result expressions based on the evaluation of a condition.

Example: CASE in SELECT


SELECT
    ProductName,
    Price,
    CASE
        WHEN Price < 10 THEN 'Low Price'
        WHEN Price BETWEEN 10 AND 50 THEN 'Medium Price'
        ELSE 'High Price'
    END AS PriceCategory
FROM
    Products;

GOTO Statement

The GOTO statement transfers control to another Transact-SQL statement within the same batch or stored procedure. It should be used sparingly as it can make code difficult to read and maintain.

Example: GOTO (Use with Caution)


DECLARE @LoopCount INT = 0;

LoopStart:
SET @LoopCount = @LoopCount + 1;
PRINT 'Iteration: ' + CAST(@LoopCount AS VARCHAR(10));

IF @LoopCount < 3
BEGIN
    GOTO LoopStart;
END

PRINT 'Loop finished.';

Introduction Data Types Operators Functions Querying Data