How to Use If Else in SQL Server Stored Procedure

When working with SQL Server, I was required to use conditional logic that allowed me to use it in different scenarios. Then, I used the IF Else condition in a stored procedure to get the required output.

How to Use If Else in SQL Server Stored Procedure

Let’s explore conditional logic in SQL Server stored procedures and see how Different IF-ELSE blocks can be used.

SQL Server stored Procedure If Else

The IF-ELSE statement in SQL Server is a conditional statement that allows you to execute or skip an SQL statement block depending on the specified condition.

To execute an IF-ELSE block in SQL Server, we can use the following syntax.

IF Boolean_expression   
     { statement_block }   
ELSE   
     { statement_block }

In the above syntax, the Boolean_expression is an expression that will either return True or False. The statement_block are Transact-SQL statements that are executed based upon Boolean_expression.

If the Boolean_expression returns TRUE, then the statement of the IF block will be executed. And if the Boolean_expression returns FALSE, then the statement of the ELSE block will be executed. It is recommended to use the control-of-flow keywords BEGIN and END to define a statement block.

Note- If the Boolean expression comprises a SELECT statement, the SELECT statement must be enclosed in parentheses.

Consider the following example given below.

Create Procedure Check_Odd_Even
( @var  int)
AS
BEGIN
IF(@var%2=0)
    Begin
	PRINT 'It is an even value'
    END
ELSE
    BEGIN
	PRINT 'It is an odd value'
    END   
END

In the example, we are using the IF-ELSE block within a stored procedure to check whether the given input is even or odd. So, the procedure will print It is an even value if the number is even. And it will print It is an odd value if the number is odd.

Now, to execute the procedure, we are using the following query.

EXEC Check_Odd_Even 21

SQL Server Stored Procedure If Else Select

While using the IF-ELSE block within a stored procedure, we often need to select some data from a table to compare the IF block’s condition. In such situations, we can use the SELECT statement in the Boolean expression of the IF-ELSE block.

However, the SELECT statement must be enclosed in parentheses while using it in the Boolean expression.

Let’s understand this implementation with an example. First, consider the following table, EmployeeInfo.

Consider the following example given below.

CREATE PROCEDURE Name_Finder1
( @name varchar(50) )
AS
BEGIN
  IF((SELECT Employeename] FROM [EmployeeInfo] 
  WHERE [Employeename] = @name) = @name)
    BEGIN
	Print 'The record with name: '+@name+' is there in the table'
    END
   ELSE
    BEGIN
	Print 'The record with name: '+@name+' is not there in the table'
    END
END

In the above example, we have created a procedure with one input variable of the varchar(50) data type. We are using this variable in the Boolean expression to find the existence of a name in the sample table. The procedure will return the PRINT statement according to the condition specified.

Here is an execution example of the above procedure.

SQL Server Stored Procedure If Else in Where Clause

The IF-ELSE clause in SQL Server is quite useful, and we can use it to obtain the desired results whenever we need to execute any conditional operation. However, IF-ELSE has some limitations, one of which is that it cannot be used in the WHERE clause.

Let’s understand this with the help of an example.

ALTER PROCEDURE Records
( @gender AS VARCHAR(50) )
AS
BEGIN
SELECT * FROM SampleTable
WHERE 
IF @gender IS NULL
     gender='Male'
ELSE
    gender=@gender
END

In the example, we are using the IF-ELSE block with the WHERE clause to filter all the male records. But the query will return the following error.

However, there are still some alternatives available in SQL Server that we can use instead of IF-ELSE. We will also discuss them with examples.

Using Case Statement

The Case statement in SQL Server returns one of several possible result expressions after evaluating a list of conditions. Moreover, any statement or clause that permits a valid expression can use CASE.

CASE can be used in SELECT, UPDATE, DELETE, and SET statements, as well as clauses like select list, IN, WHERE, ORDER BY, and HAVING.

Here is the standard syntax of the Case statement, which we can use with the WHERE clause in a stored procedure.

CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END  

For a better understanding, consider the example below for SampleTable.

CREATE PROCEDURE FilterRecords
( @gender AS VARCHAR(50) )
AS
BEGIN
SELECT * FROM SampleTable
WHERE gender =
(CASE WHEN @gender IS NULL THEN 'Male' ELSE @gender END)
END

In the above example, we have created a stored procedure to filter out records based on gender. We are using the CASE statement in the WHERE clause to handle the condition when the specified gender is NULL.

Here is the simple execution of the above procedure.

Here, it will show the list of gender(Male) available in the table.

SQL Server Stored Procedure Nested If Else

In SQL Server, we can also nest an IF-ELSE statement within another IF-ELSE statement. The maximum number of nested levels is limited by the amount of memory available.

Look at the below example.

Create PROCEDURE Nested_ifelse
( @x INT, @y INT )
AS
BEGIN
IF (@x > 0)
BEGIN
    IF (@x < @y) --Nested IF-ELSE
    BEGIN
        PRINT 'x > 0 and x < y';
    END
    ELSE
    BEGIN
        PRINT 'x > 0 and x >= y';
    END
END
 
ELSE
BEGIN
    PRINT('x < 0')
END		
END

In the example, we have created a stored procedure with 2 input parameters, x and y. First, we use the IF-ELSE block to check if the value of x is greater than 0. Then, for the IF statement, we have nested another IF-ELSE block to check if the value of x is greater than y.

Here is an execution example of the above procedure.

NOTE – Nesting an IF statement inside another statement is bad practice because it makes the code difficult to understand and maintain.

SQL Server Stored Procedure Multiple If Statements

The SQL Server also allows using multiple IF statements in a stored procedure. However, each IF statement will be independent of other IF statements. It means each IF statement’s boolean expression will be evaluated once in the stored procedure.

For a better understanding, consider the following example demonstrated below.

CREATE PROCEDURE RESULT_CALCULATOR
( @StudentMarks INT )
AS
IF @StudentMarks >= 90
  PRINT 'Congratulations, You got A+ grade';

IF @StudentMarks >= 80 and @StudentMarks < 90
  PRINT 'Congratulations, You got A grade';

IF @StudentMarks >= 70 and @StudentMarks < 80
  PRINT 'Congratulations, You got B+ grade';

IF @StudentMarks >= 60 and @StudentMarks < 70
  PRINT 'Congratulations, You got B grade';

In the above example, we have created a stored procedure that will accept the student’s marks and return a grade based on them. For this implementation, we have used multiple IF statements in the procedure.

Let’s understand how these multiple IF statements work by executing the procedure.

So, while executing the procedure, we passed 90 as input for @StudentMarks. The procedure evaluates this value for every IF block and returns the block statement for which the boolean expression is true.

While using multiple IF statements, we must be careful when specifying conditions. Without proper usage of the IF statement, we can get an unexpected result set.

Else If in SQL Server Stored Procedure

While using the else-if statement in SQL Server, we can also use the ELSE block with it. In SQL Server, ELSE is an optional statement block that will be executed with the specified condition for boolean expression return FALSE.

And, while using ELSE-IF, the ELSE block can be used to define a default value to be executed when every block in else-if returns FALSE.

Here is a general syntax that we can use to implement ELSE-IF with ELSE in a stored procedure.

IF (Expression 1)
BEGIN
   Statement 1;
END

ELSE IF (Expression 2)
BEGIN
   Statement 2;
END

ELSE 
BEGIN
   Default Statement;
END

Now for better understanding, let’s improve the RESULT_CALCULATOR procedure explained before.

So, previously, in the RESULT_CALCULATOR procedure, if we specified a false value for every ELSE-IF block, the procedure would not return anything. Now, we are going to use the ELSE block to specify a default value.

ALTER PROCEDURE RESULT_CALCULATOR
( @StudentMarks INT )
AS
IF @StudentMarks >= 90
BEGIN
 PRINT 'Congratulations, You got A+ grade';
END
ELSE IF @StudentMarks >= 80
BEGIN
 PRINT 'Congratulations, You got A grade';
END
ELSE IF @StudentMarks >= 70
BEGIN
 PRINT 'Congratulations, You got B+ grade';
END
ELSE IF @StudentMarks >= 60
BEGIN
 PRINT 'Congratulations, You got B grade';
END
ELSE
BEGIN
  PRINT 'Sorry, You got C grade'
END

Now, if we specify any value that is false for every IF and ELSE-IF block, then the ELSE block will be executed.

Conclusion

I hope this tutorial helped you in understanding SQL Server stored procedure if else condition.

You may like the following SQL server articles:

• String or binary data would be truncated in SQL Server
• Create Foreign Key in SQL Server
• Types of Backup in SQL Server
• Saving changes is not permitted in SQL Server