The IF..ELSE statements in MS SQL Server

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:

IF @table_name = 'employees'

   SELECT * FROM sto_employees;

ELSE

   SELECT * FROM sto_products;
  • 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 the 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 the “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:

/*Declaring a variable*/

DECLARE @val_chk INT;



/*Assigning a valye*/

SET @val_chk = 101;



/*Executing the IF statement*/

IF @val_chk = 101

   PRINT 'The IF statement is TRUE';



GO

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 an example below of using the ELSE statement:

The IF-ELSE CODE:

/*Declaring a variable*/

DECLARE @val_chk INT;



/*Assigning a valye*/

SET @val_chk = 50;



/*Executing the IF statement*/

IF @val_chk >= 101

   PRINT 'The IF statement is TRUE';

ELSE

   PRINT 'The IF statement is FLASE';

GO

The output of the above code:

The IF statement is FALSE

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:

/*Declaring a string variable*/

DECLARE @table_name VARCHAR(60);



/*Assigning a value*/

SET @table_name = 'employees';



/*Executing the IF statement*/

IF @table_name = 'employees'

   SELECT * FROM sto_employees;

ELSE

   SELECT * FROM sto_products;

GO

The output of the above code is:

SQL IF ELSE

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

In the 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 are required to check different table names and execute respective SELECT statements 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:

/*Declaring a string variable*/

DECLARE @table_name VARCHAR(60);



/*Assigning a value*/

SET @table_name = 'products';



/*Executing the IF statement*/

IF @table_name = 'employees'

   SELECT * FROM sto_employees;

ELSE

   BEGIN 

      IF @table_name = 'products'  

      SELECT * FROM sto_products; 

   ELSE 

      SELECT * FROM sto_emp_salary_paid; 

   END ; 

GO

You can see that 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 them in the SELECT statement as well to experience it yourself.

Going another level in the NESTED IF statement

Just to show that you may nest to many levels, I am going 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 the value of the variable is equal to “orders”. If TRUE then fetch the records from the orders table.
  • Finally, the ELSE statement for any other value of the variable should get the records of the employee’s salary paid table.

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

/*Declaring a string variable*/

DECLARE @table_name VARCHAR(60);



/*Assigning a value*/

SET @table_name = 'orders';



/*Executing the IF statement*/

IF @table_name = 'employees'

   SELECT * FROM sto_employees;

ELSE

   BEGIN 

      IF @table_name = 'products'  

      SELECT * FROM sto_products; 

   ELSE 

   BEGIN 

      IF @table_name = 'orders'  

      SELECT * FROM sto_orders; 

   ELSE 

      SELECT * FROM sto_emp_salary_paid; 

   END ;

   END ;

    

GO

As I set the value of the variable as orders, the IF statement should return the records from the 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.
Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!