Multiple filters on a WHERE clause

fhunth

Rate me:

1.63/5 (8 votes)

2 May 20056 min read

51.4K

How to write multiple filters on a WHERE clause on a sql sentence

Multiple filters on a WHERE clause

Introduction

<o:p>

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.

<o:p>

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

<o:p>

Sample

<o:p>

Taking the pubs database so as to illustrate the case.

<o:p>

We must show all employees that belongs to a publisher or thoses who has a certain job.

The User Interface looks like that:

<o:p>

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).

<o:p>

The Stored Procedure that solves the query is something like that:

<o:p>

Making several SELECT sentences<o:p>

<o:p>

CREATE PROCEDURE dbo.GetEmployeesFiltered1<o:p>

<o:p>

(<o:p>

@JobFilter int ,<o:p>

@PublisherFilter int<o:p>

)<o:p>

<o:p>

AS<o:p>

<o:p>

begin<o:p>

<o:p>

declare @FilterByJob bit<o:p>

declare @FilterByPub bit<o:p>

<o:p>

if @JobFilter > 0<o:p>

begin<o:p>

select @FilterByJob = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByJob = 0<o:p>

end <o:p>

<o:p>

if @PublisherFilter > 0<o:p>

begin<o:p>

select @FilterByPub = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByPub = 0<o:p>

end <o:p>

<o:p>

if @FilterByJob=1<o:p>

BEGIN<o:p>

if @FilterByPub=1<o:p>

BEGIN<o:p>

--Filter By Job and Pub<o:p>

SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id<o:p>

<o:p>

WHERE<o:p>

publishers.pub_id = @PublisherFilter<o:p>

OR<o:p>

jobs.job_id = @JobFilter<o:p>

<o:p>

END <o:p>

<o:p>

else<o:p>

--Filter by Job<o:p>

BEGIN<o:p>

<o:p>

SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id<o:p>

<o:p>

WHERE<o:p>

jobs.job_id = @JobFilter<o:p>

END <o:p>

<o:p>

END<o:p>

<o:p>

else<o:p>

--Does not filter by job<o:p>

<o:p>

BEGIN<o:p>

if @FilterByPub=1<o:p>

BEGIN<o:p>

<o:p>

SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id<o:p>

<o:p>

WHERE<o:p>

publishers.pub_id = @PublisherFilter<o:p>

<o:p>

END <o:p>

else<o:p>

--Does not filter by Pub<o:p>

BEGIN<o:p>

<o:p>

SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id<o:p>

<o:p>

END <o:p>

<o:p>

END<o:p>

<o:p>

end<o:p>

<o:p>

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.<o:p>

<o:p>

So that can be solved in that way<o:p>

<o:p>

Executing a built SELECT sentence via the EXEC command<o:p>

<o:p>

<o:p>

CREATE PROCEDURE dbo.GetEmployeesFiltered2<o:p>

<o:p>

(<o:p>

@JobFilter int ,<o:p>

@PublisherFilter int<o:p>

)<o:p>

<o:p>

AS<o:p>

<o:p>

begin<o:p>

<o:p>

declare @FilterByJob bit<o:p>

declare @FilterByPub bit<o:p>

<o:p>

if @JobFilter > 0<o:p>

begin<o:p>

select @FilterByJob = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByJob = 0<o:p>

end <o:p>

<o:p>

if @PublisherFilter > 0<o:p>

begin<o:p>

select @FilterByPub = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByPub = 0<o:p>

end <o:p>

<o:p>

declare @mySentece varchar (1000) <o:p>

declare @myFilter varchar (1000)<o:p>

<o:p>

select @mySentece = 'SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id'<o:p>

<o:p>

if @FilterByJob=1<o:p>

BEGIN<o:p>

if @FilterByPub=1<o:p>

BEGIN<o:p>

--Filter By Job and Pub<o:p>

SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter + '<o:p>

OR<o:p>

jobs.job_id = ' + @JobFilter<o:p>

END <o:p>

<o:p>

else<o:p>

--Filter by Job<o:p>

BEGIN <o:p>

SELECT @myFilter = 'jobs.job_id = ' + @JobFilter<o:p>

END <o:p>

<o:p>

END<o:p>

<o:p>

else<o:p>

--Does not filter by job<o:p>

<o:p>

BEGIN<o:p>

if @FilterByPub=1<o:p>

BEGIN<o:p>

<o:p>

SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter<o:p>

<o:p>

END <o:p>

else<o:p>

--Does not filter by Pub<o:p>

BEGIN <o:p>

SELECT @myFilter = ''<o:p>

END <o:p>

END<o:p>

<o:p>

end<o:p>

<o:p>

if @myFilter = ''<o:p>

begin<o:p>

exec ( @mySentece )<o:p>

end <o:p>

else<o:p>

begin<o:p>

exec ( @mySentece + ' WHERE ' + @myFilter )<o:p>

end <o:p>

<o:p>

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.<o:p>

<o:p>

Multi filter SELECT sentences<o:p>

<o:p>

And here is where becames the solution I propose to a Query with many filters<o:p>

<o:p>

CREATE PROCEDURE dbo.GetEmployeesFiltered3<o:p>

<o:p>

(<o:p>

@JobFilter int ,<o:p>

@PublisherFilter int<o:p>

)<o:p>

<o:p>

AS<o:p>

<o:p>

begin<o:p>

<o:p>

declare @FilterByJob bit<o:p>

declare @FilterByPub bit<o:p>

<o:p>

if @JobFilter > 0<o:p>

begin<o:p>

select @FilterByJob = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByJob = 0<o:p>

end <o:p>

<o:p>

if @PublisherFilter > 0<o:p>

begin<o:p>

select @FilterByPub = 1<o:p>

end <o:p>

else<o:p>

begin<o:p>

select @FilterByPub = 0<o:p>

end <o:p>

<o:p>

SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>

FROM jobs INNER JOIN<o:p>

employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>

publishers ON employee.pub_id = publishers.pub_id<o:p>

<o:p>

WHERE <o:p>

(@FilterByPub=1 AND publishers.pub_id = @PublisherFilter )<o:p>

<o:p>

OR <o:p>

<o:p>

(@FilterByJob=1 AND jobs.job_id = @JobFilter )<o:p>

End<o:p>

<o:p>

The main step is to establish which filters to “activate”. When this indicator is turned on , it will apply the WHERE search condition.<o:p>

That is the tip so as to build multiple filters on a WHERE clause.<o:p>

<o:p>

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/

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

Written By

fhunth

Web Developer

Argentina

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Multiple filters on a WHERE clause

Multiple filters on a WHERE clause

Introduction

Sample

Making several SELECT sentences<o:p>

Executing a built SELECT sentence via the EXEC command<o:p>

<o:p>

Multi filter SELECT sentences<o:p>

About fhunth

License

Comments and Discussions