SQL 2005+

SQL Server IF Conditions

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.

IF Conditions

In the earlier articles of the tutorial we have investigated the creation, population and querying of databases in varying levels of detail. All of these subjects are essential to the SQL Server developer but they only provide the building blocks of database programming, rather than the whole. Over the next several articles we will be examining some of the flow-of-control commands provided by Transact-SQL. These allow greater control over the execution of code and permit the creation of complex and powerful scripts and stored procedures. The first of these commands is the IF...ELSE statement.

IF conditions allow you to execute one or more statements only if a specified condition is met. The condition is provided as a Boolean expression, or predicate, that evaluates to either true or false. When the condition is true, the statements controlled by the IF command are executed. When the expression is not true the contained statements are not executed, though an alternative group of commands may be.

Using IF Conditions

The examples in this article use and modify the JoBS database. This is the database that has been created throughout the course of the tutorial. If you do not have an up to date copy of the database, please download and execute the creation script that is available via the link at the top of this page.

Basic IF Conditions

The basic syntax of the IF statement is as follows:

The condition element is the Boolean value or expression to be considered. If the condition is true, the statement is executed. If it is not true, the statement is ignored. A second variation on the syntax includes the ELSE clause. If the ELSE clause is provided and the condition does not evaluate as true, the statement following the ELSE is executed.

We can demonstrate the IF statement by adding a simple stored procedure to the JoBS database. The following procedure changes the amount of stock held by an engineer. The stored procedure has three parameters that allow the engineer ID, part number and amount of stock held to be specified. As the EngineerStock table may already include a row for the combination of engineer and part, the IF statement uses the EXISTS function to determine whether a new row should be inserted or an existing one updated.

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

    -- Does stock entry exist?
    IF EXISTS(SELECT * FROM EngineerStock
              WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber)
              
        -- Yes, update existing row
        UPDATE EngineerStock
        SET UnitsHeld = @NewUnitsHeld
        WHERE EngineerId = @EngineerId AND PartNumber = @PartNumber
    
    ELSE
    
        -- No, insert new row
        INSERT INTO EngineerStock
        VALUES(@EngineerId, @PartNumber, @NewUnitsHeld)

END

To test the stored procedure we need to start with an engineer and part number combination that currently has no rows. If you have not modified the data in the EngineerStock table, you should be able to execute the following to show that the engineer with an ID of 1 does not have an entry for the part number '15PIPREP'.

Executing the stored procedure for this pair of values should therefore create a new row in the table. Run the stored procedure as follows and then re-run the query to see the new row.

To check that the IF statement is working correctly, we can now run the stored procedure again but using a different final value. After executing the following statement, you should see that the existing row has been updated, rather than a new one created.

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