SQL IF ELSE Statement

The Microsoft SQL Server has the IF statement and the IF ELSE condition and both are one of the most useful decision-making queries. The IF statement will test the condition first, and if the condition is TRUE, it will execute the code (multiple statements) inside the block. It won’t provide any instructions to perform when the expression evaluated to false. On the other hand, the SQL IF ELSE condition will test the expression at the beginning, and depending upon the result, it will execute the statements. When the test condition is true, the code inside the IF block will execute. Otherwise, the statements inside the ELSE block will execute. 

SQL If Statement Syntax

The basic syntax of the If statement to check whether the condition is True is as shown below.

IF (condition or Expression)

BEGIN

-- If condition is True, Execute the Code

END

If you observe the above syntax, there is a BEGIN..END block after the IF statement and it defines the boundaries. If the expression results TRUE, the code inside the BEGIN..END block will execute. Remember, if want to print a single statement, ignore BEGIN..END whereas it is mandate for executing multiple statements or lines.

SQL IF Statement Examples

Let me show you a simple example to demonstrate the working functionality of the IF statement.

DECLARE @marks INT = 85;
IF @marks > = 80
BEGIN
   PRINT ' Congratulations ';
   PRINT ' You passed With Distinction ';
END

In the above example, first we have declared a @marks variable off integer type and set its value as 85. The If statement checks whether the @marks is greater than or equal to 80. If the expression is True, the PRINT statements inside the BEGIN..END block will execute. Here, it is TRUE, so the output is 

Congratulations
You passed With Distinction

How to Check Table Exists?

The below shown example checks whether the database has the employee table. If TRUE, drop the employee table and create a new table with the name. Otherwise, use the ELSE block to print any messages to user.

IF OBJECT_ID('dbo.employee', 'U') IS NOT NULL 
DROP employee;
GO
-- CREATE TABLE employee

How to Check Stored Procedure Exists?

The below shown example checks whether the sp_employee stored procedure exists in the database. If TRUE, drop the procedure. Otherwise, you can use the CREATE PROCEDURE a new procedure with the name or print any messages using the ELSE block.

IF OBJECT_ID ( 'sp_employee', 'P' ) IS NOT NULL   
    DROP PROCEDURE sp_employee;  
GO
-- CREATE PROCEDURE sp_employee

If you observe the above two examples, we haven’t used any BEGIN..END block because the IF THEN statement was followed by a sgbnle line DROP command.

SQL Server If statement and NOT EXISTS example

One of the most common scenario of using the if statement is in combination of EXISTS and NOT EXITS operators. For instance, the below query prints the ‘There is No Employee with ID 15’ statement if there is no EmpID 15 in the Employee table. 

IF NOT EXISTS (
	SELECT * FROM Employee WHERE EmpID = 15)
PRINT 'There is No Employee with ID 15'

Alternatively, try the EXISTS operator to print the positive message.

IF EXISTS (
	SELECT * FROM Employee WHERE EmpID = 7)
PRINT 'Employee ID already present.'
SQL IF Statement Example

SQL If Else Statement Syntax

The syntax of the If Else condition to execute a set of commands if the expression is TRUE and executing different set for the FALSE is as shown below.

IF (Test condition or Expression)
BEGIN
  -- The condition is TRUE then these will be executed
  True statements;
END

ELSE
BEGIN
   -- The condition is FALSE then these will be executed
   False statements;
END

This if else statement accepts any test condition as the argument. When the test condition or expression in the above structure is true, then True statements will execute. When the condition is false, then the False code will run.

SQL Server If Else Condition Flow chart

Let us see the flow chart of the If Else statement for a better understanding. If you are using the IF statement, there is FASLE route.Let us see the flow chart of the If Else statement for a better understanding.

If Else Statement flow chart

When the test condition is true, then STATEMENT1 will run, followed by STATEMENTN. And the condition is False, then STATEMENT2 will run, followed by STATEMENTN. Because it is out of the if else condition, and it has nothing to do with the Server condition result.

SQL Server If Else Condition Example

In this example, we are going to place four different Print lines. We will display two different messages when the expression is considered true. If the expression result is false, we will print another two messages.

--Declaring Number and Total Variables
DECLARE @Marks INT = 72 ;

IF @marks > = 50
BEGIN
   PRINT ' Congratulations ';
   PRINT ' You pass the Examination ';
END
ELSE
BEGIN
   PRINT ' You Failed ';
   PRINT ' Better Luck Next Time ';
END

OUTPUT 1: Here marks = 72. Here, the expression 72 >= 50 is TRUE. That’s why print inside the If Statement display’s the Message output.

OUTPUT 2: Here, we changed the marks variable to 42, and the expression evaluated to FALSE. That’s why messages inside the Else block are displayed as Message output.

 You Failed 
 Better Luck Next Time
SQL IF ELSE Condition for Student Marks Example

How to Use EXISTS and IF ELSE?

We have shown multiple examples of how we use the EXISTS operator in combination with the IF statement and it is an extension to the above with ELSE block. The below example checks whether there any employees whose sales is greater than 4000. If True, print  ‘Top Performers’; otherwise, print ‘Need more Effort ‘.

IF EXISTS (SELECT * FROM [Employee] WHERE [Sales] > 4000)
BEGIN 
	PRINT 'Top Performers';
END 
ELSE
	PRINT 'Need more Effort ';

The below query will check whether the Employees table exists in the Database. If TRUE, print the ‘Table Exists in Database’ message; otherwise, print ‘Table Does not Exists’ message.

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL 
BEGIN
  PRINT 'Table Exists in Database'
END
ELSE
BEGIN
PRINT 'Table Does not Exists'
END

Check Multiple Conditions using AND/OR

The Sql Server also allows you to use the AND/OR operators within the IF ELSE statement to multiple conditions. With the help of ‘AND’ or ‘OR’, you can combine two or more conditions within a single statement. For instance, in the below code.

  • age >= 18 checks whether the user age is greater than or equals to 18. The second condition checks the nationality. The AND operator combines these two conditions, and if they both are TRUE, print  Eligible to Vote in Elections message.
  • If either one of the condition returns FALSE (may be under 18 or nationality is not Indian), it prints Not allowed to Vote.
DECLARE @age INT = 20, @Nationality VARCHAR(50) = 'USA'

IF @age >= 18 AND @Nationality = 'Indian'
BEGIN
	PRINT ' Eligible to Vote in Elections '
END
ELSE
	PRINT ' Not allowed to Vote '

IF ElSE Condition Practical Example

In this SQL Server program, we are going to check whether the Employee Sales are greater than or equal to 2000 or not using our If Else Statement.

  • If the condition inside the if clause is TRUE, We are going to display the Employee records Whose Sales are Greater than or Equal to 2000
  • If the expression result is FALSE, the query returns the Employee records Whose Sales is Less than 2000

Before we start writing our query, Let us see if the data that we are going to use for this demonstration is

Let us see the code behind this SQL Server If Else Statement Example.

--Declaring Number and Total Variables
IF @Sales > = 2000
BEGIN
  SELECT [FirstName],[LastName]
      ,[Education],[Occupation]
      ,[YearlyIncome],[Sales]
  FROM [Employee]
  WHERE [Sales] >= 2000
  ORDER BY [EmpID] ASC
END

ELSE
BEGIN
SELECT [FirstName],[LastName]
      ,[Education],[Occupation]
      ,[YearlyIncome],[Sales]
  FROM [Employee]
  WHERE [Sales] < 2000
  ORDER BY [EmpID] ASC
END

OUTPUT 1: Here, we specified the Sales as 500. The expression in this If Statement is 500 >= 2000, which is FALSE. That’s why Output is displaying 6 out of 14 records whose sales are less than 2000

Here, we changed the Sales variable to 2500. The expression 2500 >= 2000 is TRUE. That’s why Output is displaying 8 out of 14 records whose sales are greater than or equal to 2000.

SQL IF ELSE Practical Example on Tables

SQL If ELSE condition inside Stored Procedures

One of the most utilization of the IF ELSE statement is within the stored procedures to return output based on the boolean result. The below stored producer accepts the product as the parameter and checks whether there is any available stock in the product inventory before allowing the user to place order.

CREATE PROCEDURE sp_checkinventory @Product_Id INT
AS
BEGIN
	DECLARE @avaibale_stock INT;
	
	SELECT @avaibale_stock = Product_quantity FROM Products WHERE Prod_id = @Product_Id;

	IF @avaibale_stock > 0
		PRINT 'Product is available in Stock';	
	ELSE
		PRINT 'Out of Stock';
END

Similarly, the below store procedure examples authorize the users to access the admin panel or any other work environment based on the access role.

CREATE PROCEDURE sp_checkUser
AS
BEGIN
	IF @server_role  = 'serveradmin'
		PRINT 'Admin Access Granted';	
	ELSE
		PRINT 'Access Denied';
END

Refactor Stored Procedure approach

While working with the IF ELSE conditions inside the store procedure, you should try the refactor approach rather than utilizing multiple conditions inside a single SP. For instance, the below stored procure will create a master or main one. Next, based on the condition result, it will call the child or other stored procedures.

CREATE PROCEDURE sp_PreviousSales
AS
BEGIN
	SELECT * FROM 2024SalesView
END

CREATE PROCEDURE sp_CurrentSales
AS
BEGIN
	SELECT * FROM 2025SalesView
END

CREATE PROCEDURE sp_SalesReport
AS
BEGIN
	IF @FinancialYear = 2025
		EXEC sp_CurrentSales;
	ELSE
		EXEC sp_PreviousSales;
END

Nested If Statement

The Microsoft SQL Server allows  you to nest one IF statement inside the other to further apply the logic at lower levels. So, the Nested If statements helps you check more complex conditions to further verify the logic inside the query.

  • You can place the If condition inside the IF block or ELSE block.
  • The nested if statement will execute only when the outer condition evaluated to TRUE.
DECLARE @Marks INT = 95 ;

IF @marks > 80
BEGIN
	IF @marks > 92
	BEGIN
		PRINT ' Congratulations ';
		PRINT ' You Got Scholarship ';
	END
	ELSE
		PRINT ' You Passed with Distinction ';
END
ELSE
BEGIN
   PRINT ' Better Luck Next Time ';
END

The first IF statement checks whether the student scored examination marks greater than 80. If it is TRUE, then the nested if condition checks whether the secured marks is greater than 92. If so, award the scholarship; otherwise, print the ‘ You Passed with Distinction ‘ message. If the student scores less than 80, the ELSE block will print the ‘ Better Luck Next Time ‘ message.

Common SQL IF Statement and IF ELSE Condition Errors: Fix them!

The following are the some of the most common errors that you might do while working with the If else. Here, we will show an example of each error and how to fix them with alternative approaches.

Using the IF statement in SELECT

In general, you might use the IF statement to check the expression within the SELECT statement and it will throw an error. For example, in the below code, we are testing whether the Gender is M or not. If true, M will replace with the Name Male; otherwise, FEMALE as the Gender description.

SELECT FirstName, Gender, 
IF(Gender = 'M', 'Male', 'Female') AS GenderDescription FROM DimCustomer;
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.

The above code will throw an error. However, you can replace the IF statement with the CASE expression to get the appropriate result.

SELECT FirstName, Gender,
CASE Gender
	WHEN 'M' THEN 'Male'
	ELSE 'Female' 
END AS GenderDescription
FROM DimCustomer;

Missing IF ESLE BEGIN END

Sometimes, users forget using the BEGIN and END to wrap the multiple statements. If you are executing a single statement within the SQL Server IF ELSE condition block, you can simply write them without the BEGIN..END. A simple example of this was already shown in the IF statement examples. However, when you want to execute multiple statements, you must wrap those statements within the BEGIN..END block.

For example, the below code will print only one line of code instead of two. It is because the if condition only protects the next single statement.

DECLARE @Marks INT = 90 ;
IF @marks > = 50
   PRINT ' Congratulations ';
   PRINT ' You pass the Examination ';

To fix the above issue, we have to utilize the BEGIN END block.

DECLARE @Marks INT = 90 ;
IF @marks > = 50
BEGIN
   PRINT ' Congratulations ';
   PRINT ' You pass the Examination ';
END

Things to Consider:

  • The execution flow of the statements within the SQL Server Nested IF ELSE should be in a correct flow; otherwise, the result should be strange.
  • Always comment the conditions so that you can understand the purpose in the future.
  • Try using CASE statement as it gives more control.
  • If your goal is to simply check TRUE or FALSE within the statement, try IIF statement.
  • Avoid excessive use of nested conditions because it makes the code complex and ineffective.
Categories SQL

Comments are closed.