SQL Server stored procedure if else

by
IF ELSE in stored procedure SQL Server

In this SQL Server tutorial, we will discuss, Different usage of IF-ELSE block in a SQL Server stored procedure and will cover the following topic:

  • SQL Server stored procedure if else
  • SQL Server stored procedure if else in where clause
  • SQL Server stored procedure if else select
  • SQL Server stored procedure if else if syntax
  • SQL Server stored procedure if else begin end
  • SQL Server stored procedure nested if else
  • SQL Server stored procedure multiple if statements
  • if elseif else in SQL Server stored procedure
IF ELSE in stored procedure SQL Server
IF ELSE in a stored procedure

SQL Server stored procedure if else

The IF-ELSE statement in SQL Server is a conditional statement that allows to either execute or skip a SQL statement block depending upon 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. And 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.

The IF-ELSE statements are generally used in batches within stored procedures and ad hoc queries. This concept is typically used in stored procedures to check for the existence of some parameter.

For demonstration, 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
Output

Read How to view stored procedure in SQL Server

SQL Server stored procedure if else begin end

The BEGIN END are control-of-flow language keywords and they are used to classify a statement block in SQL Server. A statement block is a collection of SQL statements that run at the same time. A batch is another name for a statement block.

Here is the syntax of using a BEGIN END in SQL Server.

BEGIN  
    { sql_statement | statement_block }   
END

Now, the BEGIN END statement plays an important role while defining the IF ELSE block as it is used to control the flow of statement execution. Additionally, we cannot use multiple statements in IF ELSE blocks without using BEGIN END.

For demonstration, consider the following example in which we have used 2 print statements within the IF block.

SQL Server stored procedure if else without begin end
Example

So, the convenient way to execute an IF-ELSE block is using BEGIN END statement to define the statement block. And then, use the IF-ELSE statements within the stored procedure.

Here is the correct way to implement it.

ALTER Procedure SampleTest
( @var  bit) --store either 1 or 0
AS
BEGIN
IF(@var=1)
   BEGIN
      PRINT 'Statement 1 in if block';
      PRINT 'Statement 2 in if block';
   END
ELSE
   BEGIN
      PRINT 'Statement 1 in else block';
      PRINT 'Statement 2 in else block';
   END
END

In the example, we have specified 2 print statements for each IF and ELSE block. And now if try to execute this example, we will the following result.

SQL Server stored procedure if else with begin end
Final Result

Read SQL Server move database files

SQL Server stored procedure if else select

While using the IF-ELSE block within a stored procedure, we often get into a situation where we 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.

But the SELECT statement must be enclosed in parentheses while using it in the Boolean_expression.

Let’s understand this implementation with the help of an example. And for this, first, consider the following sample table.

SQL Server stored-procedure if else select
Sample Table

Now for demonstration, consider the following example given below.

CREATE PROCEDURE Name_Finder
( @name varchar(50) )
AS
BEGIN
  IF((SELECT [first_name] FROM [SampleTable] 
  WHERE [first_name] = @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 varchar(50) data type. And, 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 using select
Execution example

Read SQL Server stored procedure naming convention and best practices

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.

SQL Server stored procedure if else in where clause
Error Message

But don’t worry, there are still some alternatives available in SQL Server that we can use instead of IF-ELSE. And 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 a standard syntax of the Case statement that we can use with the WHERE clause in a stored procedure.

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

Now for better understanding, consider the following example given below.

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. And 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.

SQL Server stored procedure case in where clause
Example Execution

Using IIF function

The IIF() is a function in SQL Server that evaluates a condition and returns a value based upon the factor of whether the condition is TRUE or FALSE. And it has the following syntax.


IIF(condition, value_if_true, value_if_false)

The IIF() function also be used to evaluate a condition in the WHERE clause within a stored procedure.

Let’s understand this implementation of using the IIF() in the WHERE clause of a store with the help of an example.

CREATE PROCEDURE FilterRecords
( @gender AS VARCHAR(50) )
AS
BEGIN
SELECT * FROM SampleTable
WHERE gender = (IIF(@gender is NULL,'Male', @gender))
END

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

This example will also operate in the same manner as explained in the CASE statement.

Read SQL Server select from stored procedure (9 Examples)

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.

For demonstration, consider the following example given below.

ALTER 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. And in the procedure, first, we are using the IF-ELSE block to check if the value of x is greater than 0 or not. And for the IF statement, we have nested another IF-ELSE block to check if the value of x is greater than y or not.

Here is an execution example of the above procedure.

SQL Server stored procedure nested if else
Execution Example

NOTE- Nesting an IF statement inside another statement is bad practice since 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. But 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 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 marks of the student and return a grade based on the marks. 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.

SQL Server stored procedure multiple if statements
Execution

So while executing the procedure, we have passed 90 as input for @StudentMarks. And in the procedure, this value will be evaluated for every IF block, and it will return 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.

Read SQL Server stored procedure output parameter

SQL Server stored procedure if else if syntax

When checking numerous conditions at once, the ElseIf statement comes in handy in SQL Server. The statements in the IF-ELSE statement are only executed if the provided condition is true or false. In reality, though, we may need to check more than two conditions. In such cases, we can utilize the ElseIf statement in SQL Server.

And for the implementation of elseif in SQL Server, we can use the following syntax.

IF (Expression 1)
BEGIN
   Statement 1;
END

ELSE IF (Expression 2)
BEGIN
   Statement 2;
END

The SQL Server else-if statement effectively handles multiple statements by processing them in order. The first condition will be checked. If the condition is TRUE, the statements in that block will be executed. If the condition is FALSE, the next one (Else If condition) will be checked, and so on.

Now for demonstration, consider the following example.

CREATE 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

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

And for execution, conder the following query and result.

SQL Server stored procedure if else if
Output

Read SQL Server INSERT INTO SELECT + Examples

if elseif else 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 specify any value which is false for every ELSE-IF block then, the procedure will 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.

if elseif else in SQL Server stored procedure
Output

You may like the following SQL server articles:

In this SQL Server tutorial, we have learned Different usage of IF-ELSE block in a SQL Server stored procedure, and we have also covered the following topic:

  • SQL Server stored procedure if else
  • SQL Server stored procedure if else in where clause
  • SQL Server stored procedure if else select
  • SQL Server stored procedure if else if syntax
  • SQL Server stored procedure if else begin end
  • SQL Server stored procedure nested if else
  • SQL Server stored procedure multiple if statements
  • if elseif else in SQL Server stored procedure