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

sql serverstored-procedures

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)?

Best Answer

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
Related Question