In SQL Server, the IF...ELSE statement is a control-of-flow keyword that allows you to execute specific blocks of code based on whether a condition is TRUE or FALSE.

In SQL, this logic is often used to check for the existence of data, validate parameters in a stored procedure, or handle errors.

1. Basic Syntax

If the condition evaluates to TRUE, the code immediately following the IF runs. If it is FALSE, the code following the ELSE (if provided) runs.

IF condition
    -- Statement to run if TRUE
ELSE
    -- Statement to run if FALSE

2. Using BEGIN…END (Essential)

By default, IF only controls the very next statement. If you want to run multiple lines of code, you must wrap them in a BEGIN...END block.

Example:

DECLARE @SalesCount INT = 150;

IF @SalesCount > 100
BEGIN
    PRINT 'Target reached!';
    UPDATE SalesBonus SET Bonus = 500 WHERE EmployeeID = 1;
END
ELSE
BEGIN
    PRINT 'Target not met.';
    UPDATE SalesBonus SET Bonus = 0 WHERE EmployeeID = 1;
END

3. Nested IF…ELSE

You can place an IF statement inside another IF or ELSE to handle more complex scenarios.

Example:

DECLARE @Score INT = 85;

IF @Score >= 90
    PRINT 'Grade: A';
ELSE IF @Score >= 80
    PRINT 'Grade: B'; -- This runs because 85 is >= 80
ELSE
    PRINT 'Grade: C or below';

4. Key Differences: IF vs. CASE

It is easy to confuse IF with the CASE expression, but they serve different purposes:

Feature IF…ELSE CASE Expression
Type Control-of-flow statement. Expression (returns a value).
Usage Used to branch execution logic. Used within SELECT, UPDATE, or WHERE.
Scope Can wrap multiple SQL statements. Only works within a single statement.

Example of CASE (for comparison):

SELECT ProductName, 
       CASE 
           WHEN Price > 100 THEN 'Expensive' 
           ELSE 'Affordable' 
       END AS Category
FROM Products;

5. Using IF with EXISTS

A very common use case is checking for the existence of data in a table before performing an action. This prevents errors like trying to insert a duplicate or deleting something that isn’t there.

Example:

IF EXISTS (SELECT 1 FROM Customers WHERE CustomerID = 5)
BEGIN
    PRINT 'Customer exists. Updating record...';
    UPDATE Customers SET LastActive = GETDATE() WHERE CustomerID = 5;
END
ELSE
BEGIN
    PRINT 'Customer not found. Creating record...';
    INSERT INTO Customers (CustomerID, Name) VALUES (5, 'New Customer');
END

6. Multiple Conditions (IF…ELSE IF)

You can chain multiple conditions together to handle more complex scenarios. SQL Server will evaluate them in order and execute the first one that is true.

Example:

DECLARE @Score INT = 85;

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

7.IF with TRY…CATCH

You place the IF logic inside the BEGIN TRY block. If the IF condition isn’t enough to prevent an error (like a sudden network drop or a constraint violation), the code jumps to the CATCH block.

Example:

BEGIN TRY
    DECLARE @Amount MONEY = 500;

    -- IF checks for a known business rule
    IF @Amount > 1000
    BEGIN
        PRINT 'Amount too high for manual processing.';
    END
    ELSE
    BEGIN
        -- This might fail due to a Database Constraint (e.g., negative balance)
        UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = 1;
        PRINT 'Transaction successful.';
    END
END TRY
BEGIN CATCH
    -- This runs only if a runtime error occurred
    PRINT 'An error occurred!';
    PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;

8. Using IF inside the CATCH Block

One of the most common patterns is using an IF statement inside the CATCH block to decide how to handle the error. For example, checking if there is an active transaction that needs to be rolled back.

BEGIN TRY
    BEGIN TRANSACTION;
        -- Perform sensitive data changes
        INSERT INTO Orders (ID, Date) VALUES (1, GETDATE());
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- IF checks if the transaction is still "open" or "uncommittable"
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT 'Transaction rolled back due to error.';
    END
    
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Message;
END CATCH;

Categorized in:

Stored Procedures,