if...else

Description

Imposes conditions on the execution of a SQL statement.

Syntax

if logical_expression [plan "abstract plan"]
	statements
[else 
	[if logical_expression] [plan "abstract plan"]
		statement]

Parameters

logical_expression

is an expression (a column name, a constant, any combination of column names and constants connected by arithmetic or bitwise operators, or a subquery) that returns TRUE, FALSE, or NULL. If the expression contains a select statement, the select statement must be enclosed in parentheses.

plan "abstract plan"

specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. Plans can only be specified for optimizable SQL statements, that is, select queries that access tables.

statements

is either a single SQL statement or a block of statements delimited by begin and end.

plan "abstract plan"

specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. Plans can only be specified for optimizable expressions in if clauses, that is, queries that access tables. For more information, see Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide.

Examples

Example 1

Prints “yes” if 3 is larger than 2:

if 3 > 2
    print "yes"

Example 2

The if...else condition tests for the presence of authors whose postal codes are 94705, then prints “Berkeley author” for the resulting set:

if exists (select postalcode from authors
    where postalcode = "94705")
    print "Berkeley author"

Example 3

The if...else condition tests for the presence of user-created objects (all of which have ID numbers greater than 100) in a database. Where user tables exist, the else clause prints a message and selects their names, types, and ID numbers:

if (select max(id) from sysobjects) < 100
    print "No user-created objects in this database" else
 begin
    print "These are the user-created objects"
    select name, type, id
    from sysobjects
    where id > 100
 end

Example 4

Since the value for total sales for PC9999 in the titles table is NULL, this query returns FALSE. The else portion of the query is performed when the if portion returns FALSE or NULL. For more information on truth values and logical expressions, see Expressions in Chapter 4, “Expressions, Identifiers, and Wildcard Characters.”

if (select total_sales
    from titles
    where title_id = "PC9999") > 100
select "true"
else 
select "false"

Usage

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

if...else permission defaults to all users. No permission is required to use it.

See also

Commands begin...end,create procedure