CodeKicks.com
Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Tuesday, March 13, 2007

Avoid Dynamic queries in Sql Server for Search functionality

Many of has might have written Dynamic queires for “Search” functionality.


This articles explains how to avoid them.


CASE 1:


Let us assume that we have a table called “mbrOrganization” in the db.


Associated with that we have a screen in application for search.


In that screen we have dropdown for “Organization” names in the screen.


If “All” is selected we have to show all the records or else just the selected one.


In such case we can use “CASE” statement in “WHERE” clause.


declare @OrgName as varchar(100)
set @OrgName = 'e'


select * from mbrOrganization
where  (
case @OrgName
          when '*' then 1 -- get everything
          else
               case when  OrgName like '%' + @OrgName + '%' then 1 else 0 end --else only get particular rows
end = 1)



instead of  writing a dynamic query like


declare @OrgName as varchar(100)
declare @Query as varchar(2000)
set @OrgName = 'e'


set @Query = 'select * from mbrOrganization where 1 =1 '


if  @OrgName != '*'
 set @Query  = @Query  + ' and  OrgName like '%' + @OrgName + '%''


exec (@Query)


 


CASE 2:


When we have to compare some set of values with a column …if we need to use “IN” in the where clause.


Then we need to write a function which returns a table.


Let us assume we have @OrgIds, a varchar variable which contains “$” seperated Ids


and we have function “SplitValues” which splitts the given string with the given seperator


CREATE FUNCTION SplitValues(@val nvarchar(2000),@separator nvarchar(1))
returns @retIds table  (id nvarchar(500))
as
 begin
  declare @index as int
   set @index = CHARINDEX(@separator,@val,0)
         if(@index > 0)
      begin
   while (@index  > 0)
   begin
   insert into @retIds values (substring(@val,0,@index))
   set @val = substring(@val,@index+1,len(@val))
   set @index = CHARINDEX(@separator,@val,0)
  
    end
 end
 else
 begin
     insert into @retIds values (@val)
 end
  return
 end


Now we have to use it in the following way


declare @OrgIds as varchar(100)
set @OrgIds = '23$345$645'


select * from mbrorganization where orgid in (select id from dbo.splitValues(@OrgIds,'$'))


instead of  writing a dynamic query like


declare @OrgIds as varchar(100)
declare @Query as varchar(2000)
set @OrgIds = '23$345$645'


set @Query = 'select * from mbrOrganization where 1 =1 '


if  @OrgId != '
 set @Query  = @Query  + ' and  OrgId in ' + @OrgIds


exec (@Query)


 


 


 

Post a Comment

Mick said...

Excellent Thanks for the post.

I am search for this like anything. Now I got it here and it is working fine.