2

I've researched this scenario for a few hours now but cannot seem to find anything that addresses it directly.

I have lots of optional parameters passed into a stored proc. I want to return a recordset that is filtered by none, one or more of the optional params passed into the stored proc.

I'm trying to produce a single query to do this.

Here's my pseudo code (SQL Server 2005):

declare @a varchar(10); set @a = null;
declare @b varchar(10); set @b = 'comm%';

select * from x
where (if @a is not null then col1 like @a)
and (if @b is not null then col2 like @b)

As you can see I only want to filter on col1 if my param (@a) has data. Likewise I only want to filter on col2 if my param (@b) has data. If @a and @b are both NULL then the query should return all rows.

I've found several threads that come close to what I want, but none of them address using the LIKE clause.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mark
  • 687
  • 7
  • 12

3 Answers3

4

You were close - use:

SELECT x.*
  FROM X x
 WHERE (@a IS NULL OR x.col1 LIKE @a)
   AND (@b IS NULL OR x.col2 LIKE @b)

But this is not recommended because it's not sargable. Dynamic SQL would be a better approach:

DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'SELECT x.*
                   FROM X x
                  WHERE 1 = 1 '

    SET @SQL = @SQL + CASE
                        WHEN @a IS NULL THEN ' ' 
                        ELSE ' AND x.col1 LIKE @a '
                      END 

    SET @SQL = @SQL + CASE
                        WHEN @b IS NULL THEN ' ' 
                        ELSE ' AND x.col2 LIKE @b '
                      END 

BEGIN

  EXEC sp_executesql @SQL, 
                     N'@a VARCHAR(10), @b VARCHAR(10)',
                     @a, @b

END
Community
  • 1
  • 1
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Some of my cols will be indexed, while others won't. Flexibility is more important that query execution, so sargability doesn't really factor into my usp. But thanks for your feedback - I'm sure it'll shed light for others on this topic :) – Mark Nov 02 '10 at 01:51
2
SELECT * FROM X WHERE (@a IS NULL OR col1 LIKE @a) AND (@b IS NULL OR col2 LIKE @b)
Phil Hunt
  • 8,404
  • 1
  • 30
  • 25
1

This will probably be the most efficient way to do what you're looking for. Filtering will only occur on a column if the value passed to the stored procedure is not NULL:

CREATE PROCEDURE sproc_example
@Col1 varchar(30) = NULL,
@Col2 varchar(30) = NULL,
@Col3 varchar(30) = NULL
AS
SELECT Col1,
       Col2,
       Col3
FROM MyTable
WHERE Col1 LIKE COALESCE(@Col1,Col1) AND
      Col2 LIKE COALESCE(@Col2,Col2) AND
      Col3 LIKE COALESCE(@Col3,Col3)
Rob Sobers
  • 20,737
  • 24
  • 82
  • 111