Transact-SQL Reference

IF...ELSE

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

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

Arguments

Boolean_expression

Is an expression that returns TRUE or 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.'

Here is the result set:

The following titles are excellent mod_cook books:
 
Title                               
----------------------------------- 
Silicon Valley Gastronomic Treats   
The Gourmet Microwave               

(2 row(s) affected)

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.

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
   IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
BEGIN
   PRINT 'The following titles are expensive mod_cook books:'
   PRINT ' '
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook' 
END