if-else-transact-sql.md

title	IF...ELSE (Transact-SQL)
description	Transact-SQl language reference for IF-ELSE statements to provide control flow in Transact-SQL statements.
author	rwestMSFT
ms.author	randolphwest
ms.reviewer
ms.date	07/11/2016
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.technology	t-sql
ms.topic	reference
f1_keywords	IF_TSQL IF
helpviewer_keywords	IF...ELSE keyword ELSE (IF...ELSE) keyword ELSE keyword IF keyword
dev_langs	TSQL
monikerRange	>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current

IF...ELSE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Imposes conditions on the execution of a [!INCLUDEtsql] statement. The [!INCLUDEtsql] statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another [!INCLUDEtsql] statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

Transact-SQL Syntax Conventions

Syntax

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

[!INCLUDEsql-server-tsql-previous-offline-documentation]

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 [!INCLUDEtsql] statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one [!INCLUDEtsql] statement.

To define a statement block, use the control-of-flow keywords BEGIN and END.

Remarks

An IF...ELSE construct can be used in batches, in stored procedures, and in ad hoc queries. When this construct is used in a stored procedure, it is frequently used to test for the existence of some parameter.

IF tests can be nested after another IF or following an ELSE. The limit to the number of nested levels depends on available memory.

Example

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
       SELECT 'Weekend';
ELSE 
       SELECT 'Weekday';

For more examples, see ELSE (IF...ELSE) (Transact-SQL).

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

The following example uses IF...ELSE to determine which of two responses to show the user, based on the weight of an item in the DimProduct table.

-- Uses AdventureWorksDW  

DECLARE @maxWeight FLOAT, @productKey INTEGER  
SET @maxWeight = 100.00  
SET @productKey = 424  
IF @maxWeight <= (SELECT Weight from DimProduct WHERE ProductKey = @productKey)   
    SELECT @productKey AS ProductKey, EnglishDescription, Weight, 'This product is too heavy to ship and is only available for pickup.' 
        AS ShippingStatus
    FROM DimProduct WHERE ProductKey = @productKey
ELSE  
    SELECT @productKey AS ProductKey, EnglishDescription, Weight, 'This product is available for shipping or pickup.' 
        AS ShippingStatus
    FROM DimProduct WHERE ProductKey = @productKey

See Also

BEGIN...END (Transact-SQL)
END (BEGIN...END) (Transact-SQL)
SELECT (Transact-SQL)
WHILE (Transact-SQL)
CASE (Transact-SQL)
Control-of-Flow Language (Transact-SQL) ELSE (IF...ELSE) (Transact-SQL)