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.
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:
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.
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:
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:
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?
For example, if the value of the variable is ‘employees’ then execute the:
If the value is products then execute:
For any other value:
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.
