IF ELSE Statement in SQL Server

The IF ELSE statement controls the flow of execution in SQL Server. It can be used in stored-procedures, functions, triggers, etc. to execute the SQL statements based on the specified conditions.

Syntax: 

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

        

Boolean_expression: A boolean expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement then it should be enclosed in parentheses.

sql_statement | statement_block: A single or multiple statements that need to be executed. To include multiple statements, enclosed them between BEGIN and END keywords.

The ELSE block is optional. If the Boolean expression with the IF statement returns FALSE, then the control is passed to the ELSE statement.

If the condition in the IF block returns TRUE, then the SQL statement block after the IF statement is executed. If the condition returns FALSE, then the control executes the ELSE block if present, or else it exits the IF statement.

Example 1

The following example demonstrates the if-else condition.

Example: IF ELSE Statement

DECLARE @mySalary INT = 5000,
        @avgSalary = 4000;

IF @mySalary > @avgSalary
	PRINT 'My Salary is above the average salary.';
ELSE
	PRINT 'My Salary is less than the average salary.';

In the above example, the IF condition @mySalary > @avgSalary checks whether the @mySalary is greater than @avgSalary The @mySalary > @avgSalary returns TRUE, so the statement below it will be executed.

Changing the value of any variable will affect the result, as shown below.

Example: IF ELSE Statement

DECLARE @mySalary INT = 5000,
        @avgSalary = 6000;

IF @mySalary > @avgSalary
	PRINT 'My Salary is above the average salary.';
ELSE
	PRINT ' My Salary is less than the average salary.';

IF ELSE Statement with SELECT Query

The following example uses the SELECT query to demonstrate the IF ELSE condition.

Example: IF ELSE with SELECT Query

if (select AVG(Salary) from Employee)  > 5000
	print 'Average salary is greater than 5000';
else
	print 'Average salary is less than 5000';

In the above example, the IF statement contains the select query in the parenthesis select AVG(Salary) from Employee and checks whether it is greater than 5000 or not. The whole condition is (select AVG(Salary) from Employee) > 5000. It displays the message based on the return TRUE or FALSE.

Nested IF Statement

The following example demonstrates the nested IF statements.

Example: Nested IF Statements

DECLARE @StudentMarks INT = 85;

IF (@StudentMarks > 80)
	BEGIN
		IF @StudentMarks > 90
			PRINT 'A+';
		ELSE
			PRINT 'A-';
	END	
ELSE 
	PRINT 'Below A grade'

In the above example, the first IF condition contains another IF ELSE condition in the block. In the outer IF condition, the variable @StudentMarks is greater than 80 then it executes in the second inner IF condition is checked, if it is greater than 90 then A+ is printed Else A- is printed.

If @StudentMarks is less than 80 and the outer IF condition returns FALSE, then the message 'Below A grade' will be displayed.