Click here to Skip to main content
Click here to Skip to main content

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

 

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

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

Share

About the Author

fhunth
Web Developer
Argentina Argentina
No Biography provided

Comments and Discussions

 
GeneralIssue PinmemberMoin Ahmed16-Feb-10 2:56 
Generalfurther simplified SQL PinmemberStephen Kelley22-May-05 15:53 
GeneralRe: further simplified SQL Pinmemberfhunth24-May-05 9:38 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 2 May 2005
Article Copyright 2005 by fhunth
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid