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
– Juan Carlos Oropeza, Commented Sep 1, 2015 at 17:07
Here is excellent article on this topic with a number of solution to make this work and remain efficient. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries — Sean Lange
– Sean Lange, Commented Sep 1, 2015 at 17:26

Sandip Bantawa · Accepted Answer · 2015-10-05 19:29:58Z

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.

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: stackoverflow.com/questions/20864934/…
I like your solution, but im wondering if the order is important? Like the one from Megatron is there any difference?

Roman Marusyk · Accepted Answer · 2015-09-01 17:12:04Z

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

Collectives™ on Stack Overflow

How apply multiple choice conditions in Select command

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related