Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers 4

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
share|improve this answer
add comment

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)
...
share|improve this answer
add comment

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.

share|improve this answer
    
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. –  Mark Storey-Smith Nov 15 '11 at 15:56
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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