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

)

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

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

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

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

)

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 + '

jobs.job_id = ' + @JobFilter

END

else

--Filter by Job

BEGIN

SELECT @myFilter = 'jobs.job_id = ' + @JobFilter

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

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

)

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 )

(@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

Sample image

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/

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here