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

The statement following an if keyword and its condition is executed if the condition is satisfied (when the logical expression returns TRUE). The optional else keyword introduces an alternate SQL statement that executes when the if condition is not satisfied (when the logical expression returns FALSE).
The if or else condition affects the performance of only a single SQL statement, unless statements are grouped into a block between the keywords begin and end (see Example 3).

The statement clause can be an execute command or any other legal SQL statement or statement block.
If a select statement is used as part of the boolean expression, it must return a single value.
if...else constructs can be used either in a stored procedure (where they are often used to test for the existence of some parameter) or in ad hoc queries (see Examples 1 and 2).
if tests can be nested either within another if or following an else. The maximum number of if tests you can nest varies with the complexity of any select statements (or other language constructs) that you include with each if...else construct.

When an alter table, create table, or create view command occurs within an if...else block, Adaptive Server creates the schema for the table or view before determining whether the condition is true. This may lead to errors if the table or view already exists.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

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