IF...ELSE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL 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 Transact-SQL statement that is executed when the IF
condition isn't satisfied: the Boolean expression returns FALSE
.
Transact-SQL syntax conventions
Syntax
IF boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Arguments
boolean_expression
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 }
Any Transact-SQL 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 Transact-SQL 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's usually 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.
Examples
IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
SELECT 'Weekend';
ELSE
SELECT 'Weekday';
For more examples, see ELSE (IF...ELSE).
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
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 INT;
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;