ELSE (IF...ELSE) (T-SQL)

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement (sql_statement) following the Boolean_expression is executed if the Boolean_expression evaluates to TRUE. The optional ELSE keyword is an alternate Transact-SQL statement that is executed when Boolean_expression evaluates to FALSE or NULL.

Syntax

IF Boolean_expression{sql_statement | statement_block}
[
    ELSE
    {sql_statement | statement_block}]

Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block). For more information, see Batches and the specific statements being used.

Result Types

Boolean

Examples

This example produces a list of traditional cookbooks priced between $10 and $20 when one or more books meet these conditions. Otherwise, SQL Server prints a message that no books meet the condition and a list of traditional cookbooks that costs less than $10 is produced.

USE pubs

GO

DECLARE @msg varchar(255)

IF (SELECT COUNT(price)

    FROM titles

    WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0

    BEGIN

     SET NOCOUNT ON

     SET @msg = 'There are several books that are a good value between $10 and $20. These books are: '

         PRINT @msg

      SELECT title

     FROM titles

     WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20

    END

ELSE

    BEGIN

     SET NOCOUNT ON

     SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.'

         PRINT @msg

     SELECT title

     FROM titles

     WHERE title_id LIKE 'TC%' AND price < 10         

    END

Here is the result set:

There are several books that are a good value between $10 and $20. These books are:

title                                                                           

------------------------------------------------------------------------

Fifty Years in Buckingham Palace Kitchens

Sushi, Anyone?

(2 row(s) affected)

See Also