Multiple filters on a WHERE clause
Introduction
Sometimes I had to develop a report or a something to show data, but previously the user�d have the possibility to apply multiple filters so as so view the info that wants to be found.
As ever I designed the User Interface with the filters then passed the filters through the different layers and finally, they came to a stored procedure that makes the query.
It returns the data and that�s all.
Several times I heard some colleagues telling that they must show data applying multiple filters and they finally at the stored procedure did not find another way to make the query than building a string and then executing it with the EXEC sql Command or making several SELECT sentences
Sample
Taking the pubs database so as to illustrate the case.
We must show all employees that belongs to a publisher or thoses who has a certain job.
The User Interface looks like that:
Where you can filter jobs choosing a job or (All jobs) , and too you can filter by publisher choosing one of then or (All Publishers).
The Stored Procedure that solves the query is something like that:
Making several SELECT sentences
CREATE PROCEDURE dbo.GetEmployeesFiltered1
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
if @FilterByJob=1
BEGIN
if @FilterByPub=1
BEGIN
--Filter By Job and Pub
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
publishers.pub_id = @PublisherFilter
OR
jobs.job_id = @JobFilter
END
else
--Filter by Job
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
jobs.job_id = @JobFilter
END
END
else
--Does not filter by job
BEGIN
if @FilterByPub=1
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
publishers.pub_id = @PublisherFilter
END
else
--Does not filter by Pub
BEGIN
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
END
END
end
The problem of that kind of procedures is that heavy to maintain and you must remember to maintain the SELECT sentence for all options/filters.
So that can be solved in that way
Executing a built SELECT sentence via the EXEC command
CREATE PROCEDURE dbo.GetEmployeesFiltered2
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
declare @mySentece varchar (1000)
declare @myFilter varchar (1000)
select @mySentece = 'SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id'
if @FilterByJob=1
BEGIN
if @FilterByPub=1
BEGIN
--Filter By Job and Pub
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter + '
OR
jobs.job_id = ' + @JobFilter
END
else
--Filter by Job
BEGIN
SELECT @myFilter = 'jobs.job_id = ' + @JobFilter
END
END
else
--Does not filter by job
BEGIN
if @FilterByPub=1
BEGIN
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter
END
else
--Does not filter by Pub
BEGIN
SELECT @myFilter = ''
END
END
end
if @myFilter = ''
begin
exec ( @mySentece )
end
else
begin
exec ( @mySentece + ' WHERE ' + @myFilter )
end
Here you solve the problem of maintaining the SELECT sentence for all options/filters, but it has the problem that SQL Server must compile the stored procedure on every execution.
Multi filter SELECT sentences
And here is where becames the solution I propose to a Query with many filters
CREATE PROCEDURE dbo.GetEmployeesFiltered3
(
@JobFilter int ,
@PublisherFilter int
)
AS
begin
declare @FilterByJob bit
declare @FilterByPub bit
if @JobFilter > 0
begin
select @FilterByJob = 1
end
else
begin
select @FilterByJob = 0
end
if @PublisherFilter > 0
begin
select @FilterByPub = 1
end
else
begin
select @FilterByPub = 0
end
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*
FROM jobs INNER JOIN
employee ON jobs.job_id = employee.job_id INNER JOIN
publishers ON employee.pub_id = publishers.pub_id
WHERE
(@FilterByPub=1 AND publishers.pub_id = @PublisherFilter )
OR
(@FilterByJob=1 AND jobs.job_id = @JobFilter )
End
The main step is to establish which filters to �activate�. When this indicator is turned on , it will apply the WHERE search condition.
That is the tip so as to build multiple filters on a WHERE clause.
About fhunth
Recently, I took a job as a C# senior developer for Huddle Group (http://www.huddle.com.ar/)
Contact me at:
-fhunth@hotmail.com
-fernando@huddle.com.ar
Visit my blog at:
http://msdevelopers.blogspot.com/
