Strategies to implement a multi-criteria filtering.

Introduction

Sometimes we're developing applications that require performing searches against database tables in SQL Server 2000/2005. The search must enable using a set of optional parameters (up to 10 parameters for example) as the main search criteria (a null value indicates not to filter the data, and a non-null value indicate to filter the data by the underlying value). Some developers use complicated T/SQL statements (a handful of if and if/else statements) to implement this requirement, thus making the code illegible. Because I have to deal with this kind of problems, now I'd like to suggest you an alternative technique that I use in my solutions.

My implementation

Let's supposed that we have the following table (see Listing 1).

create table tbDepartment
(
nDeptNo int primary key,
vcDepartmentName varchar(50) not null,
nManager int not null, vcLocation varchar(50)
);

Listing 1

Now, our requirements indicate the option to look up for department information by specifying any criteria value (the search parameters are based on the department number, department name, department manager number, and the department location) that our users want.

Now let's define the following stored procedure (see Listing 2).

create stored procedure spSearchDepartment
(
@nDeptNo int = null,
@vcDepartmentName varchar(50) = null,
@nManager int = null,
@vcLocation varchar(50) = null)
as begin
select * from tbDepartment dept where
(@nDeptNo is null OR @nDeptNo=dept.nDeptNo) and
(@vcDepartmentName is null or @vcDepartmentName = dept.vcDepartmentName)    and (@nManager is null or @nManager = dept.nManager) and
@vcLocation is null or @vcLocation = dept.vcLocation)
end

Listing 2

The underlying logic of the SQL statement in the stored procedure is to check each parameter and see if the value passed by the stored procedure invoker is null which means that the invoker don't want to filter the results by this parameter. If the parameter is not null, then value passed by the invoker is used to match the records of the tables (the normal logic of the WHERE clause).

Another similar technique is by using the COALESCE function (see Listing 3).

create stored procedure spSearchDepartment
(
@nDeptNo int = null,
@vcDepartmentName varchar(50) = null,
@nManager int = null,
@vcLocation varchar(50) = null)
as begin
select * from tbDepartment dept where
dept.nDeptNo = COALESCE(@nDeptNo, dept.nDeptNo) and
dept.vcDepartmentName = COALESCE(@vcDepartmentName, dept.vcDepartmentName)and dept.nManager = COALESCE(@nManager, dept.nManager) and
dept.vcLocation = COALESCE(@vcLocation, dept.vcLocation)
end

Listing 3

Conclusion

In this article, I discussed my implementation strategy to solutions whose requirements establish the use of multi-criteria filtering to search for information based on a set of parameters (where the null value on one parameter indicates not to apply any filter to the data).