Imposes conditions on the execution of a SQL statement.
if logical_expression [plan "abstract plan"] statements
[else [if logical_expression] [plan "abstract plan"] statement]
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.
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.
is either a single SQL statement or a block of statements delimited by begin and end.
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.
Prints “yes” if 3 is larger than 2:
if 3 > 2 print "yes"
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"
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
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"
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.
SQL92 – Compliance level: Transact-SQL extension.
if...else permission defaults to all users. No permission is required to use it.
Commands begin...end,create procedure