I recently was discussing some strategies for performing searches against database tables where a set of optional parameters can be supplied. The scenario I'm talking about is in regards to doing searches on a table based on a set of ID's within the table, not free text searches. The individual that I was discussing this with was not looking forward to writing the proc to perform the search becuase there was approximately 10 optional criteria. I proded a little to see what the problem was and found that he was planning on implementing the optional criteria by having a bunch of if/else if statements checking each of the criteria. Rather then going that route, I suggested an alternative technique.
Assume we have a table called Employee with the following fields:
EmployeeID int
DepartmentID int
ManagerID int
LocationID int
Now assume you want users of your application to be able to find an Employee by specifying any of the criteria they so desire. User1 may just want to find all employees who Joe Manager [ManagerID: 5] manages, User2 may want to find all employees who Joe Manager manages, but only if they are in the Technology Department [DepartmentID: 49]. Using the technique I shared with my co-worker would result in a proc that looks like this:
CREATE PROCEDURE ap_SearchEmployee
(
@EmployeeID int = null,
@DepartmentID int = null,
@ManagerID int = null,
@LocationID int = null
)
as
SET NOCOUNT ON
SELECT * FROM Employee e
WHERE
(@EmployeeID IS NULL OR e.EmployeeID = @EmployeeID)
AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
AND (@ManagerID IS NULL OR e.ManagerID = @ManagerID)
AND (@LocationID IS NULL OR e.LocationID = @LocationID)
The where clause checks each parameter to see if it is NULL which means we don't want to filter our results by that parameter. If the parameter is not null it then checks the fields value with that of the parameter. By using this technique my co-worker was able to remove the duplicate logic and nasty if/else blocks within his stored procedure which provided him with a much cleaner solution!