0

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.

enter image description here

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?

Improve this question
8
  • Yes, logically, this is correct. Performance issues can come from a lot of directions. The first thing you should do is look at your query plan (which will be quite large with all those joins) and look for table scans and clustered index scans. What to do next depends on what you find. Also, try commenting out the 2 and clauses for c.StartTime and c.endtime. Because you're converting them to time, those clauses make the query, "non-sargable". That basically means that searching for those clause elements cannot use an index. So they will be slow. Commented Jun 23, 2015 at 13:54
  • but those fields are in varchar. thats why i convert it to time for time comparison Commented Jun 23, 2015 at 14:01
  • Why are those fields varchar if they are time? Commented Jun 23, 2015 at 14:16
  • the application design is like that. the value is " 7:00 AM" etc.. Commented Jun 23, 2015 at 14:17
  • Changing those fields to be time would be best, but if you can't do that, then try converting the @starttime and @endtime to varchar, instead. That way the query optimizer can still try to use an index for those fields if one exists. Commented Jun 23, 2015 at 14:22

2 Answers 2

Reset to default
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 ;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

can we implement the same in where clause too?
@BaijuMohan, see the where clause in the beginning of the sentence 'select * from a where 1=1'
what is asked is, when we use multiple filters in where clause using 'and' then is it possible to use dynamic query. or do i need to write separate blocks each condition (for district id, location id, session id ) etc..
You need separate block for every condition to build 1 long dynamic query, if you want shorter code you can use XML as parameter and extract name,values and type from it
2

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.

Improve this answer

14 Comments

I have implemented the logic u mentioned before. but my code didn't work. how can i do it using dynamic query?
Well if a parameter is NULL, and you don't need to filter by it, just don't include it in the dynamic query at all. But what do you mean your code didn't work? Did you get an error?
@BaijuMohan, this is (in my opinion) the best approach in terms or readability and maintenance. If it didn't work first time out it's not because what Tab has suggested is incorrect - it's probably due to a problem with your SQL. I'd suggest you post your modified SQL and the error message you get when you try to run it.
This is an excellent approach to this problem. Gail Shaw has blogged about this very topic and demonstrates a few options for this type of query. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
@TabAlleman i got no output when i used the method u suggested
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.