Sql "IF", "BEGIN", "END", "END IF"?

sql

conditional-statements

sql-try-catch

dynamic-sql

byAnton Shumikhin·Sep 20, 2024

In SQL, the conditional logic is steered by IF...ELSE blocks or CASE expressions. Use IF...ELSE in stored procedures for intricate logic:

IF (condition)
BEGIN
    -- True branch code
END
ELSE
BEGIN
    -- False branch code
END

For inline selection, CASE is your go-to:

SELECT CASE WHEN condition THEN result1 ELSE result2 END FROM myTable;

Bear in mind that exact syntax varies as per the SQL dialect.

Mastering `IF`, `BEGIN`, `END`

The lone `IF` ranger

A straightforward IF statement suffices for checking a simple condition. However, it will only execute the next SQL statement. Code wisely:

IF @Flag = 1
    -- Leave no trace, like a ninja!
    INSERT INTO LogTable (Message) VALUES ('Flag was set to 1');

`IF...BEGIN...END` trio to the rescue when stakes are high

When executing multiple statements conditionally, wrap them within a BEGIN...END block. Consider it like a SQL safe box that treats all the nested goodies as one package:

IF @Flag = 1
BEGIN
    -- Flag is up, let's log
    INSERT INTO LogTable (Message) VALUES ('Flag is set to 1');
    -- Just like in a video game, let's up the score!
    UPDATE StatsTable SET Counter = Counter + 1;
END

`ELSE` - the unsung hero

Ensuring an ELSE for your IF is almost always beneficial. This ensures you've got a Plan B when Plan A (your IF) fails:

IF @Amount > 1000
BEGIN
    PRINT 'Transaction requires a superhero...I mean manager!';
END
ELSE
BEGIN
    PRINT 'Transaction approved. Hurray!';
    -- More code runs here
END

Exception handling: The `BEGIN TRY...CATCH` saga

When performing insert or update operations within an IF...BEGIN...END block, don't forget to shield your code from potential errors, especially within transactions:

BEGIN TRY
    BEGIN TRANSACTION
        IF @ShouldUpdate = 1
        BEGIN
            -- Let's try an update...
            COMMIT TRANSACTION
        END
        ELSE
        BEGIN
            -- And...action!
            COMMIT TRANSACTION
        END
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    -- Catch me if you can
END CATCH

Condensing control flow for pros

Morphing into a `MERGE`

The MERGE statement is your all-in-one tool for conditional insertions, updates, or deletions, side stepping complex IF structures:

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
    UPDATE SET Target.Value = Source.Value
WHEN NOT MATCHED THEN
    INSERT (ID, Value) VALUES (Source.ID, Source.Value);

Dynamic SQL: Building statements on the fly

With Dynamic SQL, you can construct variable-based conditional statements. Remember to avoid the dark side: SQL injection:

DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'SELECT * FROM Users WHERE ' + @WhereClause;  -- Like building a lego castle!
EXEC sp_executesql @DynamicSQL;

Debugging nested `IF...BEGIN...END`

Be sure to double-check the logic and syntax of nested conditional blocks, and run different test scenarios for confidence:

IF @ParentCondition = 1
BEGIN
    IF @ChildCondition = 1
    BEGIN
        -- It's true! Just like the earth is round.
    END
    ELSE
    BEGIN
        -- Flat earthers, here's your code!
    END
END

SQL Story time: Practical scenarios and debugging

The intriguing case of the conditional `INSERT`

For a real-life scenario, let's look at an INSERT operation dependent on a specific condition, and another INSERT that's completely indifferent to it:

IF @UserRole = 'Admin'
BEGIN
    -- Make way, Admin coming through!
    INSERT INTO RolesTable (UserID, RoleName) VALUES (@UserID, 'System Administrator');
END
-- This insert plays by its own rules
INSERT INTO AuditTable (UserID, Action) VALUES (@UserID, 'Attempted Role Assignment');