How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

Question

I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query.

Simply i can use IF-ELSE statement and write the select combinations in it.

Is there any way in sql to avoid this select combinations(all combinations with 4 parameters)?

Answer 1

Something like

WHERE
   (@p1 IS NULL OR SomeCol1 = @p1)
   AND
   (@p2 IS NULL OR SomeCol2 = @p2)
   AND
   (@p3 IS NULL OR SomeCol3 = @p3)
   AND
   (@p4 IS NULL OR SomeCol4 = @p4)

This will probably run slower then using separate SELECTs separated by IF. However, you do have 16 permutation. So I'd probably combined both techniques:

IF (common combination of NULL parameters #1)
 SELECT ...
ELSE IF (common combination of NULL parameters #2)
 SELECT ...
ELSE 
 SELECT using WHERE above

Answer 2

Dynamic SQL can be a pain but this is one of those situations where it can be very useful.

DECLARE @sql nvarchar(MAX)
SET @sql = N'SELECT [columns] FROM [tables] WHERE 1=1 AND'

IF NOT (@param1 is null)
   SET @sql = @sql + N' AND [condition]'

IF NOT (@param2 is null)
   SET @sql = @sql + N' AND [condition]'

If NOT (@param3 is null)
   SET @sql = @sql + N' AND [condition]'

IF NOT (@param4 is null)
   SET @sql = @sql + N' AND [condition]'

-- close out the statement
SET @sql = @sql + [order by, group by, whatever]

EXEC sp_executesql @sql

You could make it a bit easier to detect whether a param was sent or not by setting default values and testing for those instead of null values:

CREATE PROCEDURE [myprocedure] 
(
   @param1 int = 0
   @param2 varchar = 'unknown value'
   ...
)

...
IF NOT (@param1 = 0)
...

Answer 3

Erland Sommarskog, a SQL Server MVP, handles this question really well. I'd love to just cut and paste his stuff here but I won't. He has written a great couple articles (depending on the version of SQL Server you are on) about dynamic search conditions and getting the best possible performance. I've used his approach numerous times and have been pleased at the results. I +1'd Valkyrie's answer because it is heading down that path but Erland expands on it quite well.

• For SQL Server 2008 (SP1 CU5 and greater)
• For Lower Versions

Answer 4

I apologize to every one, i didn't mention the parameters were from a single column.

Using 'WHERE' clause with 'IN' solved the issue. The implementation is as follows,

  SELECT [COL1],[COL2]
  FROM [TABLE1]
  WHERE [TABLE1].COL3 IN (@PAR1,@PAR2,@PAR3,@PAR4)

This will have no issue even if any of the parameter has no value. It just neglects the parameters with 0 value from the WHERE clause.