SQL 2005+

SQL Server IF Conditions

by Richard Carr, published at http://www.blackwasp.co.uk/SQLIf.aspx

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.

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