IF...ELSE

SQL Server 2000

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax

Arguments

Is an expression that returns TRUEor FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement. To define a statement block, use the control-of-flow keywords BEGIN and END. CREATE TABLE or SELECT INTO statements must refer to the same table name if the CREATE TABLE or SELECT INTO statements are used in both the IF and ELSE areas of the IF...ELSE block.

Remarks

IF...ELSE constructs can be used in batches, in stored procedures (in which these constructs are often used to test for the existence of some parameter), and in ad hoc queries.

IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.

Examples

A. Use one IF...ELSE block

This example shows an IF condition with a statement block. If the average price of the title is not less than $15, it prints the text: Average title price is more than $15.

USE pubs
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
PRINT 'Average title price is more than $15.'

B. Use more than one IF...ELSE block

This example uses two IF blocks. If the average price of the title is not less than $15, it prints the text: Average title price is more than $15. If the average price of modern cookbooks is more than $15, the statement that the modern cookbooks are expensive is printed.