CASE Statement in WHERE Clause

So many times we come across such situations where user can pass all the parameters defined in the Stored procedure or he can pass the parameter of his choice. For example, In search screens where user  can select all the parameters or any one of the given Parameters.

We have two choices to implement this logic at DB.

       1. Constructing dynamic queries depending on the parameters passed to stored procedure

       2. By writing the CASE statement in WHERE clause.

 In this article we will look at the second option.

 Use AdventureWorks2008R2

 go

 CREATE PROC SEARCHPERSON

 (

 @BUSINESSENTITYID INT=-1,

 @PERSONTYPE VARCHAR(2)='',

 @FIRSTNAME VARCHAR(50)='',

 @MIDDLENAME VARCHAR(50)=''

 )

 AS

 BEGIN

   SELECT * FROM PERSON.PERSON WHERE

   BUSINESSENTITYID= CASE(@BUSINESSENTITYID)

   WHEN -1 THEN BUSINESSENTITYID

   ELSE @BUSINESSENTITYID

   END

   AND PERSONTYPE=CASE(@PERSONTYPE)

   WHEN '' THEN PERSONTYPE

   ELSE @PERSONTYPE

   END

   AND FIRSTNAME=CASE(@FIRSTNAME)

   WHEN '' THEN FIRSTNAME

   ELSE @FIRSTNAME

   END

   AND MIDDLENAME=CASE(@MIDDLENAME)

   WHEN '' THEN MIDDLENAME

   ELSE @MIDDLENAME

   END

 END

 Now we will execute the stored procedure with different combinations of input parameters and see the         result set

 When I don’t pass any parameter it will return all the records from table

 

 When I pass the only person type parameter value

 When I pass combination of different parameters