Skip to content

if-else-transact-sql.md

Latest commit

 

History

History
110 lines (85 loc) · 4.13 KB

if-else-transact-sql.md

File metadata and controls

110 lines (85 loc) · 4.13 KB
title IF...ELSE (Transact-SQL)
description Transact-SQL language reference for IF-ELSE statements to provide control flow.
author rwestMSFT
ms.author randolphwest
ms.date 05/17/2024
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
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 || =fabric || =fabric-sqldb

IF...ELSE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Imposes conditions on the execution of a [!INCLUDE tsql] statement. The [!INCLUDE tsql] 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 [!INCLUDE tsql] statement that is executed when the IF condition isn't satisfied: the Boolean expression returns FALSE.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="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 [!INCLUDE tsql] 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 [!INCLUDE tsql] 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: [!INCLUDE ssazuresynapse-md] and [!INCLUDE ssPDW]

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;

Related content