Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

ELSE (IF...ELSE)

SQL Server 2000

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement (sql_statement) following the Boolean_expression is executed if the Boolean_expression evaluates to TRUE. The optional ELSE keyword is an alternate Transact-SQL statement that is executed when Boolean_expression evaluates to FALSE or NULL.

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 valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Result Types

Boolean

Examples

This example produces a list of traditional cookbooks priced between $10 and $20 when one or more books meet these conditions. Otherwise, SQL Server prints a message that no books meet the condition and a list of traditional cookbooks that costs less than $10 is produced.

USE pubs
GO
DECLARE @msg varchar(255)
IF (SELECT COUNT(price)
   FROM titles 
   WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0
   
   BEGIN
     SET NOCOUNT ON
     SET @msg = 'There are several books that are a good value between $10 and $20. These books are: '
         PRINT @msg
      SELECT title 
     FROM titles 
     WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20
   END
ELSE
   BEGIN
     SET NOCOUNT ON
     SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.'
         PRINT @msg
     SELECT title 
     FROM titles 
     WHERE title_id LIKE 'TC%' AND price < 10       
   END

Here is the result set:

There are several books that are a good value between $10 and $20. These books are: 
title                                                                   
------------------------------------------------------------------------
Fifty Years in Buckingham Palace Kitchens
Sushi, Anyone?

(2 row(s) affected)

See Also

ALTER TRIGGER

Batches

Control-of-Flow Language

CREATE TRIGGER

IF...ELSE

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft