ALL IF Null logic in SQL Server

Question

We have a requirement to implement search function in a web page. So we have created a stored procedure to retrieve the records. The logic is, when i select a input parameter Sp returns filtered record for the parameter else it retrieve all the available records.

for eg: if i give,

EXEC [WB_GetClassesByLocation_new2] null,null,null,'null',null,NULL,'N','N','N','N','N','N','N',NULL,null,null,null,null,null

(N is the default value for Days field) SP Will return all the available records. if I give,

EXEC [WB_GetClassesByLocation_new2] 1000,null,null,'null',null,NULL,'N','N','N','N','N','N','N',NULL,null,null,null,null,null . SP Will return records for district 1000. i have implemented the below logic

Select distinct c.classID, co.fCourseName as CourseName, StreetAddress + ', ' + l.City as LocationAddress, s.SessionName, sh.fShift as shift, StartTime, EndTime, c.classname, s.SessionID,
        c.StartDate,c.enddate 
        From dbo.vw_Class c 
        Inner Join dbo.lk_Session s 
        On (s.SessionID = c.sessionID) 
        Inner Join dbo.lk_Course co 
        On (co.CourseID = c.CourseID )
        Inner Join dbo.vw_Location l 
        On (l.locationid = c.locationid) 
        Inner Join lk_District d
        On (d.districtID = c.districtId) 
        Inner Join lk_Province p 
        On (p.provik = d.provik) 
        Inner Join lk_Shift sh 
        On (c.shiftid = sh.shiftid)
       where 
          c.DistrictID       =  case  when @Districtid is null   then c.DistrictID   else  @Districtid  end 
         and c.LocationID    =  case  when @locationid is null   then c.LocationID   else  @locationid  end 
         and s.SessionID     =  case  when @sessionid is null    then s.SessionID    else  @sessionid   end 
         and c.CourseID      =  case  when @levelid  is null     then c.CourseID     else  @levelid     end 
         and c.ShiftID       =  case  when @shiftid   is null    then c.ShiftID      else  @shiftid     end 
         and c.StartDate    >=  case  when @startdate is null    then c.StartDate    else  @startdate   end
         and c.EndDate      <=  case when  @enddate is null      then c.EndDate      else  @enddate     end
         and convert(time,c.StartTime) >= case when @starttime is null then convert(time,c.StartTime) else convert(time,@starttime) end
         and convert(time,c.endtime)   <= case when @endtime is null then convert(time,c.endtime) else convert(time,@endtime) end
         and c.Monday    = case  when @day1 = 'N' then c.monday     else  @day1  end 
         and c.Tuesday   = case  when @day2 = 'N' then c.Tuesday        else  @day2  end 
         and c.Wednesday = case  when @day3 = 'N' then c.Wednesday  else  @day3  end 
         and c.Thursday  = case  when @day4 = 'N' then c.Thursday       else  @day4  end 
         and c.Friday    = case  when @day5 = 'N' then c.Friday     else  @day5  end 
         and c.Saturday  = case  when @day6 = 'N'then c.Saturday        else  @day6  end 
         and c.Sunday    = case  when @day7 = 'N' then c.Sunday     else  @day7  end 
         and c.RowStatus    = 'A' 
         ORDER BY co.fCourseName, s.SessionID ,c.ClassName

But the Sp takes too much time to execute. IS this the correct way to implement the "All IF null" logic in sql server? Any other way to do the same?

Answer 1

Here's how I do it:

( @Districtid is null OR c.DistrictID = @Districtid )
AND
( @Locationid is null OR c.LocationID = @Locationid )
AND
...

However, when you have a really large number of these filters, and you're close enough to the tipping point that I would experiment, you're better off creating a dynamic SQL query.

Answer 2

When you have query with lot's of filters, I know 2 options:

1. Use OR (as suggested above), which can cause the query optimizer to scan the table. See this post for further information :How to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)

2. Use dynamic SQL - which can cause the query optimizer to calculate the execution plan on every single change in every parameter A good option is to use dynamic sql with Bind Variables, this way the optimizer will cache the query and the execution plan.

   declare @sql varchar(500)
   DECLARE @ParmDefinition nvarchar(500);
   
   SET @sql='select * from a where 1=1 '
    if @Locationid  is null
   set @sql=@sql +' and @LocationId is null'
    else
    set @sql=@sql +' and LocationID= @LocationId'
   
     EXECUTE sp_executesql @sql, N'@LocationId int',
                 @LocationID ;