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.

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'.

SELECT * FROM EngineerStock WHERE PartNumber = '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.

SetEngineerStock 1, '15PIPREP', 10

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.

SetEngineerStock 1, '15PIPREP', 5

Statement Groups

Often you will wish to include more than 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