How apply multiple choice conditions in Select command

Question

I have a Table Tab1. I want to make a stored procedure, in which I will take up to 3 parameters from the user and select data from the table using the AND operator. Like:

Select * from Tab1
Where Para1=1 AND  Para2=1 AND Para3=4

But I have a condition that the user can pass one, or two, or all three parameters. I want to write a single query such that if he passes all three parameters, then select data according these 3 parameters using the AND operator; if he passes any two parameters, select data according to those two parameters using the AND operator. Lastly, he may pass a single parameter, so then select data according this single parameter.

Is any way to write a single query for above requirement?

So if function get `para1` that have to be 1? or you mean field `para1 = @function_parameter_1`? — Juan Carlos Oropeza, Sep 01 '15 at 17:07
Here is excellent article on this topic with a number of solution to make this work and remain efficient. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ — Sean Lange, Sep 01 '15 at 17:26

Sandip Bantawa · Answer 1 · 2015-10-05T19:29:58.633

1

SELECT *
FROM Tab1
WHERE (@Para1 IS NULL OR (Para1 = @Para1))
    AND (@Para2 IS NULL OR (Para2 = @Para2))
    AND (@Para3 IS NULL OR (Para3 = @Para3))
OPTION (RECOMPILE);

So how is this possible, its because in OR short-circuits, i.e. when @Para1 is null (assuming default is null when there is no value) it doesn't go to second condition i.e. Para1 = @Para1, might be due to performance reason coz first is already satisfied which is what OR actually means i.e. to check if any clause is satisfied and similarly with rest of logic Or you can do dynamic query too

Adding to comment below by KM.

It better using OPTION (RECOMPILE), then the execution plan won't be reused coz the select depends hugely on parameters here which are dynamic so adding OPTION (RECOMPILE) would re-generate execution plan.

edited Oct 05 '15 at 19:29

answered Sep 01 '15 at 17:10

Sandip Bantawa

2,822
4
31
47

2

try adding "OPTION (RECOMPILE)" onto the query. It will basically take the runtime values of these local variables into account when it compiles the query. This allows it to remove unnecessary parts like "null=null". The simplified query is much more likely to be able to use an index, possibly resulting in a vast performance gain. see: http://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why – KM. Sep 01 '15 at 17:23
I like your solution, but im wondering if the order is important? Like the one from Megatron is there any difference? – Juan Carlos Oropeza Sep 01 '15 at 17:23

score 0 · Accepted Answer · answered Sep 01 '15 at 17:12

Try something like:

CREATE PROCEDURE usp_Test
  @param1 int = NULL
, @param2 int = NULL
, @param3 int = NULL
AS
BEGIN
  SELECT * FROM Tab1
  WHERE (Para1 = @param1 OR @param1 IS NULL)
    AND (Para2 = @param2 OR @param2 IS NULL)
    AND (Para3 = @param3 OR @param3 IS NULL)
END

How apply multiple choice conditions in Select command

2 Answers2