SQL 2005+

SQL Server IF Conditions (2)

The fifty-fourth part of the SQL Server Programming Fundamentals tutorial investigates the use of IF...ELSE statements. These are flow-of-control commands that can be used to conditionally execute one or more Transact-SQL (T-SQL) statements.

Statement Groups

Often you will wish to include more that one statement in either the IF or ELSE sections. To do so, you can use the BEGIN and END commands to surround the group of statements to execute. These keywords allow a series of commands to be executed where only one statement would normally be expected. For example, we can update the stored procedure to output messages when executed in SQL Server Management Studio by running the following:

ALTER PROCEDURE SetEngineerStock
    @EngineerId INT,
    @PartNumber CHAR(8),
    @NewUnitsHeld DECIMAL(6,2)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM EngineerStock
              WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber)
    BEGIN          
        UPDATE EngineerStock
        SET UnitsHeld = @NewUnitsHeld
        WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber

        PRINT 'Stock updated.' 
    END
    ELSE
        BEGIN
        INSERT INTO EngineerStock
        VALUES(@EngineerId, @PartNumber, @NewUnitsHeld)

        PRINT 'Stock created.' 
    END
END

Nesting

To create more complex conditional structures, IF statements can be nested. The level of nesting permitted is limited by the amount of memory available on the server executing the code. Usually the nesting limit is not exceeded, as the code required to do so would be excessively complicated and difficult to read and maintain. It is possible to exceed the limit, however, when using stored procedures that call other procedures recursively.

To demonstrate the use of nested IFs, consider the following modification to the example stored procedure. In this case, the first IF statement checks if the new stock level is zero. If it is, any rows for the engineer and part number combination are deleted from the table. If the new value is not zero, a row is created or updated according to the result of the expression of the second IF statement, which is nested in the outer command's ELSE block. NB: Although not required, the BEGIN and END commands are included for clarity.

ALTER PROCEDURE SetEngineerStock
    @EngineerId INT,
    @PartNumber CHAR(8),
    @NewUnitsHeld DECIMAL(6,2)
AS
BEGIN
    SET NOCOUNT ON;

    IF @NewUnitsHeld = 0
    BEGIN
        DELETE FROM EngineerStock
        WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber
    END
    ELSE
    BEGIN
        IF EXISTS(SELECT * FROM EngineerStock
                  WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber)
        BEGIN          
            UPDATE EngineerStock
            SET UnitsHeld = @NewUnitsHeld
            WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber
        END
        ELSE
        BEGIN
            INSERT INTO EngineerStock
            VALUES(@EngineerId, @PartNumber, @NewUnitsHeld)
        END
    END
END