IF Constructs

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Transact SQL Language Elements - Loops & Conditional Processing

Just like any programming language, T-SQL offers a way to control the flow of the program with conditional constructs. Unlike some other languages, T-SQL does not use a THEN keyword - it is always implied if a specified condition is true. T-SQL also omits the END IF, unlike other languages, for marking the end of the conditional statement. If the specified condition is false then SQL Server skips the statement or block immediately following the condition and executes the next statement or block. 

There are multiple variations of IF statements:
  • Simple IF statements
  • IF EXISTS construct
  • IF / ELSE construct
  • Nested IF statements
  • IF NOT construct


The simple IF construct is used to evaluate a Boolean condition and execute an appropriate set of commands. For instance the following example determines whether the current day of the week is Friday: 
IF DATEPART(dw, GETDATE()) = 6
        BEGIN
        PRINT 'TGI Friday'
        END

We can easily extend the same construct by adding an ELSE clause to take the alternative course of action and calculate the number of days before Friday: 

IF DATEPART(dw, GETDATE()) = 6
        BEGIN
        PRINT 'TGI Friday'
        END
  ELSE
        BEGIN
        SELECT 'Sorry, Friday is ' + CONVERT(VARCHAR(1),         (6-DATEPART(dw, GETDATE())) )+             ' days away'
        END

Alternatively we can determine the Boolean value of a condition based on the outcome of a SELECT statement: 

IF (SELECT COUNT(*) FROM authors) > 20       PRINT 'authors table contains more than 20 records'


Note: If you wish to execute multiple statements depending on the value of a condition, be sure to enclose those statements within a BEGIN END block

ometimes you have to check for multiple conditions and control your program accordingly. You can do so by combining these conditions with AND and OR within the simple IF construct. The following example demonstrates two conditions combined with AND operator: 

IF (SELECT COUNT(*) FROM authors) > 20
       AND (SELECT COUNT(*) FROM authors WHERE state = 'ca')>0
       PRINT 'authors table contains more than 20 records'
       SELECT CAST((SELECT COUNT(*) FROM authors
                  WHERE state = 'ca') AS VARCHAR(2)) +       ' of them are from california'

The previous query will return the number of authors in CA only if the authors table contains any authors from that state and only if the total number of authors is greater than 20. Therefore the AND operator returns true only if both parts of the condition are true.

You can also combine conditions with the OR operator, which checks both conditions and returns TRUE if one of them is TRUE or if both of them are true. The OR operator returns FALSE if both parts of the condition are FALSE. Although you can use the OR operator within a simple IF construct it is not recommended to do so. If you have to check for multiple conditions combined with OR logic, it is recommended to use either nested IF statements or CASE operator. 

IF EXISTS construct checks for the existence of a record fitting a specified criterion or any records in the specified table and takes action accordingly. This is a great approach to take if you don't have to retrieve any values from a table. Since you cannot do any data retrieval with EXISTS, the SELECT statement within the EXISTS clause does not have to specify any column names - the "*" operator will suffice. In fact IF EXISTS will be more efficient than IF SELECT. The following example checks whether there are any authors from KS in the authors table: 
IF EXISTS (SELECT * FROM authors WHERE state = 'ks')  PRINT 'found author(s) from KS'


As we noted earlier, if you have to check multiple conditions you can use nested IF ELSE statements. These should be handled with care and appropriately indented to make the code readable. If you've used any other programming language you'll find T-SQL nested IFs very easy to get used to. The following example executes appropriate user stored procedures depending on the price of "abc" stocks: 
DECLARE @stock_price SMALLMONEY
SELECT @stock_price = stock_price FROM high_yield_stock  WHERE stock_symbol = 'abc'
IF @stock_price > 10.01
       BEGIN
       EXEC usp_alert_for_high_prices
       END
  ELSE
        IF @stock_price BETWEEN 5.01 and 10.00
            BEGIN
            EXEC usp_notify_managers
            END
       ELSE
            EXEC usp_alert_for_price_drop

If the outer condition evaluates to TRUE, the inner conditions will not be checked. This is especially useful if the evaluation criteria require multi-table joins or searches which are resource intensive. 

You also have an option of negating or reversing the evaluation criteria within an IF statement. This is done by adding NOT keyword to the IF statement. This technique is useful if you don't have to do resource intensive work to evaluate the criteria. Otherwise it is better to use the regular IF ELSE construct. The negation operator will impose some performance overhead with resource intensive tasks. The following example determines whether there are any sales records from 1995 in the pubs database: 

IF NOT (SELECT COUNT(*) FROM sales WHERE ord_date
       BETWEEN '1/1/95' AND '12/31/95') > 0
     BEGIN
       PRINT 'no sales have been recorded in 1995'
     END
Retrieved from "http://sqlserverpedia.com/wiki/IF_Constructs"