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?

Solution 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

Solution 2

Try this code.. Here I am storing the procedure name (that is to be executed) in a variable named @sql and at the end executing this variable using sp_executesql

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

AS
BEGIN
SET NOCOUNT ON

DECLARE @sql AS NVARCHAR(MAX)

set @sql = case 
   when ((@Input_Param1 IS NOT NULL) AND (@Input_Param2 IS NOT NULL))
   then 'dbo.usp_SPWithParam1And2'

   when ((@Input_Param1 IS NOT NULL) AND (@Input_Param2 IS NULL))
   then 'dbo.usp_SPWithParam1Only'

   when((@Input_Param1 IS NULL) AND (@Input_Param2 IS NOT NULL))
   then 'dbo.usp_SPWithParam2Only'

   when ((@Input_Param1 IS NULL) AND (@Input_Param2 IS NULL))
   then 'dbo.usp_SPWithoutParam1And2'

   END

   print @sql
   EXEC sp_executesql @sql
end

Solution 3

I'm afraid your team leader is wrong.

To quote the MSDN:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

The Transact-SQL control-of-flow language keywords are (no CASE there):

 - BEGIN...END
 - RETURN
 - BREAK
 - TRY...CATCH
 - CONTINUE
 - WAITFOR
 - GOTO label
 - WHILE
 - IF...ELSE

On the other hand your query could be improved (no need to check the other IF(s) if the condition is satisfied:

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 
ELSE IF ((@Input_Param1 IS NOT NULL) AND (@Input_Param2 IS NULL))
   BEGIN
       EXEC dbo.usp_SPWithParam1Only
   END
ELSE IF ((@Input_Param1 IS NULL) AND (@Input_Param2 IS NOT NULL))
   BEGIN
       EXEC dbo.usp_SPWithParam2Only
   END  
ELSE IF ((@Input_Param1 IS NULL) AND (@Input_Param2 IS NULL))
   BEGIN
       EXEC dbo.usp_SPWithoutParam1And2
   END

EDIT - due to dynamic query CASE

That all being said there is a way to force a CASE behaviour via dynamic query like the one posted by Sushil Sharma.

There is a catch however! Using dynamic query, when it is not required, will give any potential attacher one more attacking vector via SQL Injection so in my eyes it is better to use a simple solution IF ... ELSE IF which executes already predefined procedures.

Solution 4

Tell your lead that he is wrong (in a more polite way, perhaps). There is no CASE statement in T-SQL, only a case expression. So keep using them IF that you have now.

View more solutions

Comments

[Ivan Starostin]

sp_executesql is not required here. SP call by name is done via exec @spname

[Ivan Starostin]

This is funny. The answer he is wron!! was double upvoted and accepted, and the answer giving literally (almost) what was asked - is downvoted.