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. For more information, 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

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.
If you create tables with varchar, nvarchar, univarchar, or varbinary columns whose total defined width is greater than the maximum allowed row size, a warning message appears, but the table is created. If you try to insert more than the maximum number bytes into such a row, or to update a row so that its total row size is greater than the maximum length, Adaptive Server produces an error message, and the command fails.

When 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.