Using CASE Statement in Stored Procedure

Question

I have a Stored Procedure that calls other Stored Procedures depending on the Input parameters specified.

This is how it looks:

CREATE PROCEDURE dbo.usp_SPCaller
@Input_Param1 NVARCHAR(100) = NULL,
@Input_Param2 NVARCHAR(100) = NULL

AS
BEGIN
SET NOCOUNT ON    

IF ((@Input_Param1 IS NOT NULL) AND (@Input_Param2 IS NOT NULL))
   BEGIN
       EXEC dbo.usp_SPWithParam1And2
   END 

IF ((@Input_Param1 IS NOT NULL) AND (@Input_Param2 IS NULL))
   BEGIN
       EXEC dbo.usp_SPWithParam1Only
   END

IF ((@Input_Param1 IS NULL) AND (@Input_Param2 IS NOT NULL))
   BEGIN
       EXEC dbo.usp_SPWithParam2Only
   END

IF ((@Input_Param1 IS NULL) AND (@Input_Param2 IS NULL))
   BEGIN
       EXEC dbo.usp_SPWithoutParam1And2
   END

After presenting this to our lead, he advised me to use CASE STATEMENTS instead because using IF STATEMENTS is a bad idea.

I tried searching everywhere on how to use CASE STATEMENTS in the same format as I have above but to no avail. All I find is using CASE STATEMENTS together with UPDATE.

My question

How do I use CASE STATEMENTS in SQL Server in a way that it looks like the above?

Answer 1

Your lead is wrong. In fact there is no such thing as a case statement in SQL Server, it is a case expression.

"The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures."

Reference