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

4 Answers 4


Something like

   (@p1 IS NULL OR SomeCol1 = @p1)
   (@p2 IS NULL OR SomeCol2 = @p2)
   (@p3 IS NULL OR SomeCol3 = @p3)
   (@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)
ELSE IF (common combination of NULL parameters #2)
 SELECT using WHERE above

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)

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.

  • I was just digging out a link to that article, beat me to it! Erland's article is the canonical source for this, I've found nothing that covers the topic in equal depth. Commented Nov 15, 2011 at 15:56

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,


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.