SQL Server stored procedure case statement

In this tutorial, we are going to learn about SQL Server stored procedure case statement. Here we will discuss how to use a CASE statement in SQL Server Server Stored Procedure. And we will also discuss the following topics.

• SQL Server stored procedure case statement
• SQL Server stored procedure case statement in where clause
• SQL Server stored procedure case statement example
• Execute stored procedure in case statement SQL Server

But before understanding how to use a CASE statement in a stored procedure, we should know what is a stored procedure.

SQL Server stored procedure case statement

A CASE statement in SQL Server is like a conditional statement. The CASE statement evaluates the set of conditions and returns one result when a condition is satisfied.

Now, in SQL Server, an expression in a CASE statement can be in 2 formats.

1. Simple CASE expression: It is used to evaluate an expression to some simple expressions to decide the result.
2. Searched CASE expression: It is used to evaluate number to boolean expressions to determine the value.

Now, a CASE statement in SQL Server can be used in any clause or statement that allows a valid expression. And this statement also has an optional ELSE argument. Let’s take a look at the syntax of using a CASE statement in SQL Server.

--Simple CASE expression
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   

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

Now, let’s understand some of the key arguments in the given syntax.

• input_expression: It represents any valid input expression. And it is used and evaluated in the case of the simple CASE format.
• when_expression: It is also a simple valid expression that input expression is compared to while using simple CASE format. Moreover, both input_expression and when_expression should be of the same data type.
• result_expression: This is a resulting value that is returned if the comparison of input_expression and when_expression is evaluated to be TRUE. It is also returned when the boolean_expression is evaluated to be TRUE.
• else_result_expression: This expression is returned when no comparison operation returns TRUE. It is an optional argument that can be used in both CASE statement formats. If we do not use this argument and all the comparisons are FALSE then, the CASE statement will return NULL.
• Boolean_expression: It represents any valid boolean expression. And it is used and evaluated in the case of the searched CASE format.

SQL Server stored procedure case statement example

Now, let’s understand how to use this CASE statement in a stored procedure in SQL Server. And to understand this, we will illustrate a simple example.

USE [sqlserverguides]
GO

CREATE PROC QuantityCheck
( @Quantity INT )
AS
BEGIN
  SELECT CASE 
  WHEN (SELECT @Quantity)>50 THEN 'The quantity is greater than 50'
  WHEN (SELECT @Quantity)=50 THEN 'The quantity is 50'
  ELSE 'The quantity is under 50'
  END AS [Quantity Check]
END

Note: We can use the CASE statement in any clause or statement that allows a valid expression. For example, SELECT, WHERE, HAVING, etc.

In the above example, we have created a simple stored procedure that will return a statement based upon the given input.

So, in the example, first, we have declared an input parameter of integer data type. And then, in the body, we are using the SELECT statement to have a CASE statement. And in the CASE statement, we have defined 2 boolean expressions and one else statement.

Now, let’s execute the above procedure using the following query.

USE [sqlserverguides]
GO

EXEC QuantityCheck 60
EXEC QuantityCheck 10
EXEC QuantityCheck 50

And after executing the above query, we will get the following result.

Read: SQL Server find text in stored procedure

SQL Server stored procedure case statement in where clause

In SQL Server, we can use the CASE statement in any clause or statement that allows a valid expression. This also includes the WHERE clause.

So, in this section, we will understand how to use a CASE statement in a stored procedure using a WHERE clause. And we will demonstrate this implementation using an example.

Now, for the example demonstration, we are going to use the dbo.Students_info table. And it has the following data in it.

Now, for the example implementation, we will create a stored procedure that will return the student records based upon the factor whether the student is pass or fail. And for this implementation, we will use the CASE statement in the WHERE clause.

The code for this implementation is given below.

CREATE PROC usp_GetStudentsByMarks
( @remark VARCHAR(10) )
AS
BEGIN

SELECT [id], [first_name], [last_name], [marks] FROM dbo.Students_info
WHERE marks > CASE WHEN @remark = 'Fail' THEN 0
                   WHEN @remark = 'Pass' THEN 33
			END
			AND
      marks < CASE WHEN @remark = 'Fail' THEN 33
                   WHEN @remark = 'Pass' THEN 100
			END
END

In the above example, first, we have created an input parameter @remark of data type VARCHAR(10). This parameter will be used to pass the “Pass” or “Fail” value to the stored procedure.

After this, we have used the SELECT statement to fetch 4 columns from the table, and we filter the records using the WHERE clause. Now, in the WHERE clause, we have used the CASE statement to check for a condition. And based upon that condition, the WHERE clause will filter the records.

Now, let’s see the execution of this stored procedure using the following query.

USE [sqlserverguides]
GO

EXEC usp_GetStudentsByMarks 'Pass'
EXEC usp_GetStudentsByMarks 'Fail'

In the execution, we have executed the stored procedure 2 times with different inputs. And it will return the following output.

Read: Full-text search in SQL Server

Execute stored procedure in case statement SQL Server

Till now, we have seen how to use a CASE statement in a stored procedure in SQL Server. But, can we use a stored procedure in a CASE statement.

So, the answer to the question is No. In SQL Server, we cannot use a CASE expression to control the flow of execution of a stored procedure or a user-defined function.

And even if try to execute a stored procedure in a CASE statement, the SQL Server will return an error.

Alternative method to execute stored procedure in case statement un SQL Server.

But there is one alternative method for this execution. Let’s understand this method using an example. And for this example, consider the following SQL query.

DECLARE @value VARCHAR(10) = 'Female',
        @sql VARCHAR(MAX)

SET @sql =  CASE @value
  WHEN 'Male' THEN 'MaleStudents'
  ELSE 'FemaleStudents'
  END

EXEC @sql

In the example, first, we have declared 2 variables @value and @sql. And we are using the @value as an input expression for the CASE statement. And we are using the second variable to store the result returned from the CASE statement.

Now, we have specified 2 conditions in the CASE statement. So, if the @value is set to Male then, the MaleStudents stored procedure will be returned. And if the @value is set to Female then, the FemaleStudents procedure will be returned.

In the end, we are using the EXECUTE statement to execute the stored procedure whose name is stored in the variable.

Here is the output of the above-explained SQL code.

So, we hope this method helps execute a stored procedure in a CASE statement.

You may also like to read the following SQL Server tutorials.

• How to check if SQL Server is running
• How to see view definition in SQL Server
• How to test stored procedure in SQL Server
• How to get list of users in SQL Server
• SQL Server scheduled stored procedure
• Loop in SQL Server stored procedure
• SQL Server stored procedure parameters
• SQL Server stored procedure return value

So, in this tutorial, we have discussed how to use a CASE statement in SQL Server Server Stored Procedure. And we have also discussed the following topics.

• SQL Server stored procedure case statement
• SQL Server stored procedure case statement in where clause
• SQL Server stored procedure case statement example
• Execute stored procedure in case statement SQL Server