Superdopey’s Techblog

To filter a table with one query you can do the following:

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 = -1,
@DepartmentID int = -1,
@ManagerID int = -1,
@LocationID int = -1
)

as

SET NOCOUNT ON

SELECT * FROM Employee e
WHERE
(@EmployeeID = -1 OR e.EmployeeID = @EmployeeID)
AND (@DepartmentID = -1 OR e.DepartmentID = @DepartmentID)
AND (@ManagerID = -1 OR e.ManagerID = @ManagerID)
AND (@LocationID = -1 OR e.LocationID = @LocationID)

The where clause checks each parameter to see if it is -1 which means we don’t want to filter our results by that parameter. If the parameter is not -1 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!

source