The preferred T-SQL method if the condition is to improve reuse of the query plan

Question

I want to understand what is the best method for implementing the IF condition inside a stored procedure.

I saw how this method was widely used. Which is comparable to iterative coding ...

declare @boolExpression bit = 1 --True if @boolExpression = 1 select column from MyTable where group = 10 else select column from MyTable where group = 20 

I prefer to use a set-based method ...

 declare @boolExpression bit = 1 --True select column from MyTable where group = 10 and @boolExpression =1 union all select column from MYTable where group = 20 and @boolExpression =0 

I prefer to use this method because, as I understand it, it creates a reusable query plan and fewer cache cache failures. Is it a fact or fiction? What is the right method to use.

Thank you in advance

Answer 1

Assuming you are missing UNION ALL It is not as much as I can see. The first version will cache the plan for each statement as children of the COND statement, so only the corresponding one will be called at run time.

The second will have both branches as children of the concatenation operator. Filters have the Expression Expression predicate, which means that every search is evaluated if required.

Answer 2

You can also use it as follows:

 DECLARE @boolExpression BIT = 1 SELECT column FROM MyTable WHERE CASE WHEN @boolExpression = 1 THEN CASE WHEN group = 10 THEN 1 ELSE 0 END ELSE CASE WHEN group = 20 THEN 1 ELSE 0 END END = 1 

I know this looks complicated, but it does the trick, especially in cases where applying a parameter to a stored procedure is optional.