IF Else

The IF...ELSE statement is a control-flow statement that allows you to execute or skip a statement block based on a specified condition.

 IF statement

The following illustrates the syntax of the IF statement:

1234	IF boolean_expression   BEGIN    { statement_block }END

In this syntax, if the Boolean_expression evaluates to TRUE then the statement_block in the BEGIN...END block is executed. Otherwise, the statement_block is skipped and the control of the program is passed to the statement after the END keyword.

Note that if the Boolean expression contains a SELECT statement, you must enclose the SELECT statement in parentheses.

The following example first gets the sales amount from the sales.order_items table in the sample database. Then, it prints out a message if the sales amount is greater than 1 million.

123456789101112131415161718

BEGIN    DECLARE @sales INT;     SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2018;     SELECT @sales;     IF @sales > 1000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 1,000,000’;    ENDEND

The output of the code block is:

1	Great! The sales amount in 2018 is greater than 1,000,000

Note that you have to click the Messages tab to see the above output message:

 IF ELSE statement

When the condition in the IF clause evaluates to FALSE and you want to execute another statement block, you can use the ELSE clause.

The following illustrates the IF ELSE statement:

12345678

IF Boolean_expressionBEGIN    — Statement block executes when the Boolean expression is TRUEENDELSEBEGIN    — Statement block executes when the Boolean expression is FALSEEND

Each IF statement has a condition. If the condition evaluates to TRUE then the statement block in the IF clause is executed. If the condition is FALSE, then the code block in the ELSE clause is executed.

See the following example:

12345678910111213141516171819202122

BEGIN    DECLARE @sales INT;     SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2017;     SELECT @sales;     IF @sales > 10000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 10,000,000’;    END    ELSE    BEGIN        PRINT ‘Sales amount in 2017 did not reach 10,000,000’;    ENDEND

In this example,

First, the following statement set the total sales in 2017 to the @sales variable:

1234567

SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2017;

Second, this statement returns the sales to the output:

1	SELECT @sales;

Finally, the IF clause checks if the sales amount in 2017 is greater than 10 million. Because the sales amount is less than that, the statement block in the ELSE clause executes.

12345678

IF @sales > 10000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 10,000,000’;    END    ELSE    BEGIN        PRINT ‘Sales amount in 2017 did not reach 10,000,000’;    END

The following shows the output:

1	Sales amount did not reach 10,000,000

Nested IF...ELSE

SQL Server allows you to nest an IF...ELSE statement within inside another IF...ELSE statement, see the following example:

123456789101112

BEGIN    DECLARE @x INT = 10,            @y INT = 20;     IF (@x > 0)    BEGIN        IF (@x < @y)            PRINT ‘x > 0 and x < y’;        ELSE            PRINT ‘x > 0 and x >= y’;    END END

In this example:

First, declare two variables @x and @y and set their values to 10 and 20 respectively:

12	DECLARE @x INT = 10,        @y INT = 20;

Second, the output IF statement check if @x is greater than zero. Because @x is set to 10, the condition (@x > 10) is true. Therefore, the nested IF statement executes.

Finally, the nested IF statement check if @x is less than @y ( @x < @y). Because @y is set to 20,  the condition (@x < @y) evaluates to true. The PRINT 'x > 0 and x < y'; statement in the IF branch executes.

Here is the output:

1	x > 0 and x < y

It is a good practice to not nest an IF statement inside another statement because it makes the code difficult to read and hard to maintain.