Using different set of WHERE clauses in stored procedure depending on Parameter value

Question

I have 2 stored procedures which return the same columns that I am trying to merge into a single procedure. They both have a different set of parameters and both have different WHERE clauses, but they use the same tables and select the exact same rows.

WHERE clause 1: (uses @UIOID, and @Level)

WHERE   (   @UIOID = CASE   WHEN @Level = 'Single' THEN C.C_UIOID_PK        
                WHEN @Level = 'Children' THEN CLC.UIOL_P
                WHEN @Level = 'Parent' THEN CLP.UIOL_C
            END 
        OR  (   @UIOID = '0'    
            AND @Level = 'All'          
            )
            )

Where clause 2: (Uses @TeamCode, @Year, @IncludeQCodes)

WHERE   C.C_IsChild = 0
AND C.C_MOA <> 'ADD'
AND @TeamCode = C.C_OffOrg
AND C.C_Active = 'Y'
AND (   @Year BETWEEN dbo.f_GetAcYearByDate(C.C_StartDate) AND dbo.f_GetAcYearByDate(C.C_EndDate)
        OR @Year = 0    )
AND (   C.C_InstCode NOT LIKE 'Q%'
        OR  @IncludeQCodes = 1    )

Ideally I want to add a new parameter which basically tells it which of the two WHERE clauses to run, but I can't seem to recreate that with CASE statement because as far as I can tell, they only work for a single WHERE clause, not a whole set of different clauses

I want to do this without having to repeat the select statement again and putting the whole thing in IF statements, and i don't want to put the query into a string either. I just want one select statement ideally.

The problem with using temp tables is the query itself takes a while to run without any parameters and is used in a live website, so I don't want it to have to put all records in a temp table and then filter it.

The problem with using a CTE is you can't follow it with an IF statement, so that wouldn't work either.

Here is the sort of logic I am trying to achieve:

SELECT  A
        B
        C    
FROM    X
IF @WhichOption = 1 THEN
    WHERE   (   @UIOID = CASE   WHEN @Level = 'Single' THEN C.C_UIOID_PK        
                WHEN @Level = 'Children' THEN CLC.UIOL_P
                WHEN @Level = 'Parent' THEN CLP.UIOL_C
            END 
        OR  (   @UIOID = '0'    
            AND @Level = 'All'          
            )
        )
ELSE IF @WhichOption = 2 THEN 
    WHERE   C.C_IsChild = 0
    AND C.C_MOA <> 'ADD'
    AND @TeamCode = C.C_OffOrg
    AND C.C_Active = 'Y'
    AND (   @Year BETWEEN dbo.f_GetAcYearByDate(C.C_StartDate) AND     dbo.f_GetAcYearByDate(C.C_EndDate)
                OR @Year = 0    )
    AND (   C.C_InstCode NOT LIKE 'Q%'
            OR  @IncludeQCodes = 1  )

Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — EzLo, Feb 08 '19 at 10:32
do you need a IF statement in the WHERE at all? Could you create what you want using boolean login in the WHERE? e.g SELECT * FROM tab WHERE @WhichOption = 1 AND ([logic 1]) OR WhichOption = 2 AND ([logic2]) — Cato, Feb 08 '19 at 10:36
you can't use IF in SELECT commands, you can use CASE, IIF, or WHERE — Cato, Feb 08 '19 at 10:38
Don't use such [catch-all](https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/) queries. They result in bad execution plans and *slower* performance. You'll have to add `WITH RECOMPILE` to avoid that. Use an ORM to generate queries dynamically or dynamic SQL taking care to use parameters in the final execution instead of eg appending values in the conditions — Panagiotis Kanavos, Feb 08 '19 at 10:43
What does `f_GetAcYearByDate` do? Unless it's a very, very simple cast to `date`, it will prevent the query optimizer from using any indexes on C_StartDate and C_EndDate, resulting in a full table scan. — Panagiotis Kanavos, Feb 08 '19 at 10:48
If you have so different conditions though, you probably need different procedures. You gain nothing from stuffing all combinations in a single stored procedure — Panagiotis Kanavos, Feb 08 '19 at 10:55
In some instances I have 3 versions of the same procedure but the paramaters are different. When a new column is needed I have to change all 3. I would rather it be merged into one query — user1431743, Feb 08 '19 at 11:03
@user1431743 use a view in that case. Or an ORM on the client side. In any case, all that code is just *3* optional conditions. Both the client-side ORM code and the dynamic sql needed to generate the `WHERE` statement wouldn't be complicated — Panagiotis Kanavos, Feb 08 '19 at 11:05
I can't use a view because without any parameters supplied, the query takes too long to run. What do you mean by the just 3 optional conditions? There conditions between the 2 options are completely different — user1431743, Feb 08 '19 at 11:12

StepUp · Answer 1 · 2019-02-09T21:01:13.620

Try to use dynamic SQL:

DECLARE @sql NVARCHAR(max), @where NVARCHAR(max), @WhichOption INT = 1;

SET @sql = 'SELECT  A
        B
        C    
FROM    X';

IF @WhichOption = 1
    SET @where = 'WHERE   (   @UIOID = CASE   WHEN @Level = ''Single'' THEN C.C_UIOID_PK        
                WHEN @Level = ''Children'' THEN CLC.UIOL_P
                WHEN @Level = ''Parent'' THEN CLP.UIOL_C
            END 
        OR  (   @UIOID = ''0''    
            AND @Level = ''All''
            )
        )';
ELSE IF @WhichOption = 2 
    SET @where = ' WHERE   C.C_IsChild = 0
    AND C.C_MOA <> ''ADD''
    AND @TeamCode = C.C_OffOrg
    AND C.C_Active = ''Y''
    AND (   @Year BETWEEN dbo.f_GetAcYearByDate(C.C_StartDate) 
        AND dbo.f_GetAcYearByDate(C.C_EndDate)
                OR @Year = 0    )
    AND (   C.C_InstCode NOT LIKE ''Q%''
            OR  @IncludeQCodes = 1  ) ';

SET @sql = CONCAT(@sql,' ',  @where)
PRINT @sql
EXECUTE sp_executesql @sql

Dynamic SQL removes the need for the first option. There's no need to generate a `CASE WHEN @Level` when you already know the value of `@Level`. The second option can be simplified in the same way too — Panagiotis Kanavos, Feb 08 '19 at 10:46
@PanagiotisKanavos Thank you for your advice! But I cannot figure out how I can do it. Do you mean write `CASE` statements before @where variable? It would be really cool if you write some pseudo code. — StepUp, Feb 09 '19 at 09:50

score 0 · Answer 2 · answered Feb 08 '19 at 10:43

Save the following process in a procedure. You can also directly insert into a physical table.

declare @varTable Table (columns exactly as Procedures return)

if(condition is met)
  begin 
       insert into @varTable
       exec proc1
  end 
else 
  begin 
       insert into @varTable
       exec proc2
  end

Marc Guillot · Answer 3 · 2019-02-08T14:00:11.670

0

Add the parameter that you said that it would indicate what filter apply :

select XXXXX
from XXXXX
where (@Mode = 1 and ( filter 1 ))
      or
      (@Mode = 2 and ( filter 2 ))  

option(recompile)

If the @Mode parameter is 1 then it will evaluate the filter 1, otherwise it will evaluate the filter 2.

Add an option(recompile) at the end of the statement, so the SQL engine will replace the variables with their values, eliminate the filter that won't be evaluated, and generate an execution plant for just the filter that you want to apply.

PS: Please notice that although these catchall queries are very easy to code and maintain, and generate a perfectly functional and optimal execution, they are not advised for high-demand applications. The option(recompile) forces the engine to recompile and generate a new execution plan at every execution and that would have a noticeable effect on performance if your query needs to be executed hundreds of times per minute. But for the occasional use it's perfectly fine.

edited Feb 08 '19 at 14:00

answered Feb 08 '19 at 10:53

Marc Guillot

6,090
1
15
42

That's what the OP already does. Apart from the code's complexity, the query optimizer will generate execution plans based on the first invocation, which can result in execution plans that are *not* suitable for other combinations. It's even worse with such "modes", as the execution plan is guaranteed to be bad for the other mode – Panagiotis Kanavos Feb 08 '19 at 10:54
And `WITH RECOMPILE` won't fix the complexity – Panagiotis Kanavos Feb 08 '19 at 10:56
Yes, I was just updating the answer to address the execution paln issue. – Marc Guillot Feb 08 '19 at 10:56
@PanagiotisKanavos why a recompile wouldn't fix the execution issues ?, with a recompile the engine eliminates the unnecesary clauses using lazy evaluation, so the resultant SQL would be exactly the same as with dynamic SQL. – Marc Guillot Feb 08 '19 at 10:59
It won't improve execution plan reuse. It won't fix the *complexity* and maintainability problems. The first option is a simple `WHERE C.C_UIOID_PK =@UIOID`. Only the field name changes. The execution plan for this dynamic query will be cached and reused. – Panagiotis Kanavos Feb 08 '19 at 11:02
In the second case, there are only *two* simple conditions that may or may not appear. *All* this code, in the end maps to just 3 optional conditions – Panagiotis Kanavos Feb 08 '19 at 11:03
The execution plan reuse usually is not a problem at all. You have to call a query a lot of times for the savings of reusing a plan being noticeable. What really matters is that the engine uses an optimal execution plan, which it does using this structure. – Marc Guillot Feb 08 '19 at 11:08
Quite the opposite, it's the only performance benefit of using stored procedures. Querying a lot of times is exactly what databases are for. Calling the same query 100 times a minute isn't unusual, even for a relatively small web site – Panagiotis Kanavos Feb 08 '19 at 11:11
And it only matters when you need to run that code hundreds of times per minute, indeed, which doesn't seem to be the case on this question. – Marc Guillot Feb 08 '19 at 11:12
Running a query 100s of times would take only a couple of minutes in a web site or business application with few concurrent users – Panagiotis Kanavos Feb 08 '19 at 11:13
@PanagiotisKanavos, ok, I have updated the answer to clarify that it shouldn't be used on high-demand applications, and why. But on the more common scenarios these catchall queries are perfectly fine for queries that will only be executed occasionally. – Marc Guillot Feb 08 '19 at 11:21

Using different set of WHERE clauses in stored procedure depending on Parameter value

3 Answers3