How can you use search filters that can take multiple search values?

Question

I'm trying to generate reports that has multiple search filters and I need those search filters to be able to take multiple values as search parameters.

Create Proc [procedure_name](
  @name nvarchar(50) null,
  @center nvarchar(3)null,
  @branch nvarchar(6)null
)
As
Begin
  Select [column1], [column2], [column3], [column4]
  from table1 a
  left join table2 b
  on a.cid=b.cid and a.acc=b.acc
  Where @name is null or a.Name=@name
    and @center is null or a.center=@center
    and @branch is null or a.branch=@branch
End

I need those search parameters to be able to take multiple values and filter them in the same table i.e. instead of @name = 'Mark' gives all result that has Mark I need @name = 'Mark', 'James', 'Adam' that give all results that has Mark, James and Adam.

There's a pretty full answer of your options here: https://dba.stackexchange.com/questions/144352/passing-comma-separated-values-to-parameters-in-stored-procedure — Matt Evans, Jun 20 '19 at 08:51
@MatthewEvans thank you for that but that just makes it way too complicated for what I need to do. — Nischal Sikhrakar, Jun 20 '19 at 11:20

Marc Guillot · Accepted Answer · 2019-06-20T09:59:50.840

Use an SplitString function, like this one : T-SQL split string

Now your query can filter multiple values if you pass them separated by commas. @name = 'Mark,James,Adam'

Instead of :

@name is null or a.Name=@name

use :

@name is null or a.Name in (select Name from dbo.splitstring(@name))

PS: by the way, when using this kind of catch-all statements I always add option(recompile) at the end, so the SQL engine recalculates the execution plan depending on the parameters that you have passed.

score 0 · Answer 2 · answered Jun 20 '19 at 10:59

First, your existing logic is wrong. It should be:

where (@name is null or a.Name = @name) and
      (@center is null or a.center = @center) and
      (@branch is null or a.branch = @branch)

For your fix:

where (@name is null or a.Name in (select value from string_split(@name)) and
      (@center is null or a.center in (select value from string_split(@center)) and
      (@branch is null or a.branch in (select value from string_split(@branch))

How can you use search filters that can take multiple search values?

2 Answers2