Click here to Skip to main content
12,887,921 members (46,872 online)
Click here to Skip to main content
Add your own
alternative version

Stats

42.1K views
17 bookmarked
Posted 2 May 2005

Multiple filters on a WHERE clause

, 2 May 2005
Rate this:
Please Sign up or sign in to vote.
How to write multiple filters on a WHERE clause on a sql sentence

Multiple filters on a WHERE clause


Introduction

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><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> 

<o:p>

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

<o:p> 

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

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

            <o:p>

            <o:p>

            <o:p>

            <o:p>

            <o:p>

            <o:p>

            <o:p>

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

            <o:p>

<o:p> 

<o:p> 

      end<o:p>

<o:p> 

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

<o:p> 

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

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

            <o:p>

            <o:p>

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

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

      <o:p>

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

      <o:p>

      <o:p>

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

<o:p> 

      end<o:p>

<o:p> 

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

      <o:p>

<o:p> 

<o:p> 

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

<o:p> 

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

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

            <o:p>

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

<o:p> 

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

<o:p> 

<o:p> 

<o:p> 

About fhunth

https://www.codeproject.com/useritems/pclprinting/Soldier.JPG

 

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

Share

About the Author

fhunth
Web Developer
Argentina Argentina
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralIssue Pin
Moin Ahmed16-Feb-10 1:56
memberMoin Ahmed16-Feb-10 1:56 
Generalfurther simplified SQL Pin
Stephen Kelley22-May-05 14:53
memberStephen Kelley22-May-05 14:53 
GeneralRe: further simplified SQL Pin
fhunth24-May-05 8:38
memberfhunth24-May-05 8:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 2 May 2005
Article Copyright 2005 by fhunth
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid