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, you must enclose the select statement 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 be specified only for optimizable SQL statements, that is, select queries that access tables. See Chapter 16, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide: Optimizer and Abstract Plans.

statements

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

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” of Reference Manual: Building Blocks.

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

Usage

NoteWhen a create table command occurs within an if...else block or a while loop, Adaptive Server creates the schema for the table before determining whether the condition is true. This may lead to errors if the table already exists. To avoid this situation, either make sure a view with the same name does not already exist in the database or use an execute statement, as follows:

if not exists
     (select * from sysobjects where name="my table")
begin
execute ("create table mytable (x int)")
end

Standards

ANSI SQL – 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