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
