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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

You may like the following SQL server articles:
- SQL Server DateTime vs Datetime2
- 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
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.