The IF..ELSE statements in MS SQL Server

Generally speaking, the IF..ELSE is a decision making statement in various programming languages. The IF statement is used to execute a block of code if a condition is satisfied. If a condition is not satisfied (FALSE) then optionally ELSE statement can be used.

In the case of SQL Server, the IF statement is used to execute SQL statements if a condition is TRUE.

For example:

In the above code, the @table_name is a variable. If a value assigned to this variable is “employees”, the IF statement will retrieve the data from the sto_employees table. If the value of the variable is any other than “employee” the IF condition becomes FALSE and ELSE part will execute.

I will show you this simple usage in the examples below with output. In the last section, I will also explain how to fulfill“ELSE IF” purpose which is not available in SQL Server.

A simple example of using IF statement without ELSE

Let me start with a simple example of using the IF SQL statement. We will evaluate an int type variable to check its value. If the condition is TRUE, the IF statement should print a statement.

The code:

The output of the above code is:

“The IF statement is TRUE”

As such the value of the variable is 101.

What if the condition was FALSE?

If the above condition was FALSE e.g. the value of the variable was other than 101, then nothing would happen.

In the real-world scenario, you may want to execute a statement or perform some action as the condition in the IF statement is TRUE or FALSE.

For that, use the ELSE statement. The statement inside the ELSE statement executes if the condition in the IF statement is not satisfied.

See a demo below of using the ELSE statement:

The IF ELSE CODE:

The output of the above code is: “The IF statement is FALSE” as the value of the variable is not greater than or equal to 101.

Executing a SELECT statement based on VARCHAR value

Now, let me show an example of using a string value in the IF statement. A string variable is declared and assigned a value.

In the IF…ELSE statements, we will check the value of the variable and execute the respective SELECT statement. Have a look:

The output of the above code is:

SQL IF ELSE

What if we have multiple choices to check? (NESTED IF)

In above examples, we had only two options. Either value is TRUE or FALSE. If TRUE, the statement inside the IF executes, otherwise, the ELSE part executes.

In the case of executing SELECT statements, let us say we require to check different table names and execute respective SELECT statement for each; then how can we do that?

The answer is using the NESTED IF statement.

For example, if the value of the variable is ‘employees’ then execute the:

SELECT * FROM sto_employees;

If the value is products then execute:

SELECT * FROM sto_products;

For any other value:

SELECT * FROM sto_emp_salary_paid;

See how this is translated into NESTED IF statements in SQL:

You can see, inside the ELSE statement, the BEGIN keyword is used where another IF statement is placed (the nested IF). This is terminated by using the END keyword.

There, we placed another condition to check. Try different values for the @table_name variable by your own table names and replace in the SELECT statement as well for experiencing it yourself.

Going another level in NESTED IF statement

Just for showing that you may nest to many levels, I am gone further by using another nested IF statement.

So, the scenario is to check the value of a variable (table_name). if the value is products then retrieve the data from sto_products table. If the value is employees then get the sto_employees records. The third if checks if the value of the variable is orders then fetch the records from the orders table.

Finally, the ELSE statement for any other value of the variable should get the records of employee’s salary paid table.

See the multiple nested IF statement for three conditions plus ELSE:

As I set the value of the variable as orders, the IF statement should return the records from sto_orders table.

In the code, you can see the third IF statement is started after the second BEGIN keyword.

Note: If you want to use conditional execution in the SELECT statement then use the CASE statement in SQL.