CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Koosh
Koosh

Reputation: 896

Dynamic WHERE clause in a stored procedure

I'm trying to find the best possible way to have a dynamic WHERE clause in my stored proc.

I have a few parameters that are passed into the STORED PROC from another process, the one I need to use is called @Param and it can either be 'M' or 'D'

I'm trying to use the @Param to distinguish whether it is 'M' or 'D' and based on the value I want to be able to add a filter. Simplified query below:

Select Val1, Val2, Val3, Val4 from a tbl1 
where 1=1 and SomeField in ('APPLE','HP')
--Now I'm thinking - One way of doing this - I'm not sure if I need all this...
AND 1 = (CASE WHEN (@Param='M' and Val1 <= @ParamDate) THEN 1
          WHEN (@Param='D' and Val1 <= DATEADD(mm,DATEDIFF(mm,-1,@ParamDate),-1)) THEN 1
          ELSE 0
     END)

--Another way I'm thinking is
AND ((Val1 <= @ParamDate and @Param = 'M') or NOT @Param='M')
AND ((Val1 <= DATEADD(mm,DATEDIFF(mm,-1,@ParamDate),-1) and @Param='D') or NOT @Param='D')

AND --Other parts of the where clause

I'm trying to find the best possible way of doing this. Either one of them makes more sense or maybe there's a different way of accomplishing this. I'm looking to do this in the most practical way and to have it be readable as this is a very long query.

Upvotes: 0

Views: 546

Answers (1)

GMB
GMB

Reputation: 222682

I would recommend using boolean logic rather than a case expression. This makes the intent clearer. If I followed you correctly, the logic should be:

select val1, val2, val3, val4 
from a tbl1 
where 
    somefield in ('apple','hp')
    and (
        (@param='m' and val1 <= @paramdate) 
        or (@param='d' and val1 <= dateadd(mm,datediff(mm,-1,@paramdate),-1))
    )

The inner parentheses around the and conditions are actually not needed, but using them makes it easier to understand how conditions intricate.

Upvotes: 1

Related Questions