CODEWITHSUNDEEP
sqlsql-serversql-server-2008query-optimizationwhere-clause
Matt
Matt

Reputation: 273

SQL optimise conditions in where clause

In asp I have setup several dropdowns which allow the user to filter down the data displayed on the page based on their selection.

These dropdowns are for a Data Collection, a year group eg Year 11, a subject eg English, a teaching group and a subgroup.

The Data Collection, Year and Subject must all be selected, but the teaching group and subgroup can remain unselected (select all), selected individually or both selected.

My where clause in an SQL stored procedure is now huge because of the many options under subgroup. Below are a couple of options where no subgroup is selected or the subgroup of Girls is selected or the subgroup of Boys is selected. There are many more than this, but for the sake of brevity I've limited what I've posted.

My question is can I optimise this code in any way or am I at its most concise? If you need more examples let me know and I'll add them to the post.

where 

--All
(@TeachingGroup = 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName)  AND @Subgroup='Select All'
OR 
@TeachingGroup <> 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName) AND ([TeachingGroup] = @TeachingGroup)  AND @Subgroup='Select All')
or
--Gender Girls
(@TeachingGroup = 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName)  AND Gender = 
case when @Subgroup = 'GenF' then 'F' end)
OR 
@TeachingGroup <> 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName) AND ([TeachingGroup] = @TeachingGroup)  AND Gender = case when @Subgroup = 'GenF' then 'F' end
or
--Gender boys
(@TeachingGroup = 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName)  AND Gender = 
case when @Subgroup = 'GenM' then 'M' end)
OR 
@TeachingGroup <> 'Select All' AND ([StuYear] = @StuYear) AND ([DataCollection] = @DataCollection) AND ([Name] = @SubjectName) AND ([TeachingGroup] = @TeachingGroup)  AND Gender = case when @Subgroup = 'GenM' then 'M' end

Upvotes: 2

Views: 544

Answers (1)

Laurence
Laurence

Reputation: 10976

If you get many more criteria, you'll probably want to investigate dynamic SQL, where you build different queries depending on what is supplied.

Also, it would be fairly standard practice to pass 'Null' to mean 'Select All'

However, this is a simpler version of what you have:

Where
    [StuYear] = @StuYear And
    [DataCollection] = @DataCollection And
    [Name] = @SubjectName And (
        @TeachingGroup = 'Select All' Or 
        [TeachingGroup] = @TeachingGroup
    ) And (
       @SubGroup = 'Select All' Or
       Gender = Case 
               When @SubGroup = 'GenF' Then 'F'
               When @SubGroup = 'GenM' Then 'M'
           End
    )

Dynamic sql example

Declare 
    @params nvarchar(max) = '
@StuYear int,
@DataCollection varchar(30),
@SubjectName varchar(30),
@TeachingGroup varchar(30),
@SubGroup varchar(30)', --replace with your definitions
    @sql nvarchar(max) = '
Select 
    xxx 
from 
    xxx 
Where
    [StuYear] = @StuYear And
    [DataCollection] = @DataCollection And
    [Name] = @SubjectName'

If @TeachingGroup != 'Select All'
   Set @sql += ' And TeachingGroup = @TeachingGroup'

If @SubGroup != 'Select All'
   Set @sql += ' And Gender = Right(@SubGroup, 1)'

Exec sp_executesql @sql, @params, 
    @StuYear, @DataCollection, @SubjectName, @TeachingGroup, @SubGroup

Upvotes: 1

Related Questions