Stored procedure with null parameters

• _simon_

  SSCrazy

  Points: 2329

  More actions

  September 16, 2009 at 12:55 am

  #138198

  Hello,

  what is the best way to query a table within stored procedure with null parameters (example below)? I don't know why, but I don't think that my way of doing it is the best way... Does my query (where clause) use indexes, could it be optimized?

  Thanks

  if object_id('temp') is not null drop table temp

  create table temp (col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10))

  create clustered index IX_temp1_col1 on temp (col1)

  create index IX_temp1_col2 on temp (col2)

  create index IX_temp1_col3 on temp (col3)

  create index IX_temp1_col4 on temp (col4)

  go

  if exists (select 1 from sys.objects where name = 'GetSearchData' and type = 'p') drop procedure GetSearchData

  go

  create procedure GetSearchData

  @col1 varchar(10) = null,

  @col2 varchar(10) = null,

  @col3 varchar(10) = null,

  @col4 varchar(10) = null

  as

  begin

  select col1, col2, col3, col4

  from temp

  where

  col1 like case when @col1 is null then col1 else '%' + @col1 + '%' end

  and col2 like case when @col2 is null then col2 else '%' + @col2 + '%' end

  and col3 like case when @col3 is null then col3 else '%' + @col3 + '%' end

  and col4 like case when @col4 is null then col4 else '%' + @col4 + '%' end

  order by col1

  end

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 16, 2009 at 1:29 am

  #1054273

  There a wonderful article on Erland Sommarskog's blog that could shed some light:

  http://www.sommarskog.se/dyn-search-2005.html

  Take the time to read it, you will not repent.

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• _simon_

  SSCrazy

  Points: 2329

  More actions

  September 16, 2009 at 1:50 am

  #1054280

  Thanks, very useful 🙂