CASE inside WHERE Clause

Question

I have a query in which I need to get results from a table, depending on the parameters specified by the user on a vb.net page.

Declaring a variable would be the ideal solution, but since its an inline table-valued function I can't do this, I had read on other questions that this is possible in a multstatment table-function but that the use of such function is not recommended.

The following query would be the ideal solution or what i want to achieve.

 USE [AUDIT]
 GO

ALTER FUNCTION [dbo].[GetCreated_LPA_Audits]
(   
@fechaInicio nvarchar (max),
@fechaFin nvarchar (max)
@Period int,
@Fecha int

)
RETURNS TABLE 
AS
RETURN 
(

SELECT T1.Plant,T1.Area,T1.Location,T1.IDAudit,T1.Auditor,T1.AuditDate,T1.DueDate,T1.CreationDate
FROM Header AS T1 inner join Audits AS T2 ON T1.IDChecklist = T2.IDChecklist
inner join AuditGroups AS T3 ON T2.IDGroup = T3.IDGroup 

  WHERE T3.IDGroup = '2' and CreationDate is not null AND
   CASE WHEN @Periodo = '0' THEN CreationDate>= @fechaInicio
        WHEN @Periodo = '1' THEN DATEPART(MONTH,CreationDate)>= @Fecha
        WHEN @Periodo = '2' THEN 
                               CASE WHEN @Fecha = 13 THEN 
                                                     DATEPART(MONTH,CreationDate)>= 1
                               END
        WHEN @Periodo = '2' THEN 
                               CASE WHEN @Fecha = 14 THEN 
                                                     DATEPART(MONTH,CreationDate)>= 7
                               END
        WHEN @Periodo = '3' THEN 
                               CASE WHEN @Fecha = 15 THEN 
                                                     DATEPART(Year,CreationDate)>= 2015
                               END
   END
   AND

CASE WHEN @Periodo = '0' THEN  @fechaInicio<=CreationDate
     WHEN @Periodo = '1' THEN  @Fecha<=DATEPART(MONTH,CreationDate)
     WHEN @Periodo = '2' THEN 
                               CASE WHEN @Fecha = 13 THEN 
                                                     6<=DATEPART(MONTH,CreationDate)
                               END
        WHEN @Periodo = '2' THEN 
                               CASE WHEN @Fecha = 14 THEN 
                                                     12<=DATEPART(MONTH,CreationDate)
                               END
        WHEN @Periodo = '3' THEN 
                               CASE WHEN @Fecha = 15 THEN 
                                                     DATEPART(Year,CreationDate)>= 2015
                               END
   END

AND In the previous query @fechaInicio, @fechaFin, @Periodo and @Fecha are parameters provided by the user.

Basically if @Periodo = 0 I need to get the results where CreationDate>= @fechaInicio and CreationDate<=@fechaFin

If @Periodo = 1 I need to get the results where DATEPART(MONTH,CreationDate)>= @Fecha and DATEPART(MONTH,CreationDate)<= @Fecha

And so on. Hope I made myself clear, thanks in advance!

EDIT using pseudo-code from @MatBailie, with some slights alterations and answers to his questions

IF @periodo = '0' THEN
    WHERE CreationDate  >= @fechaInicio  -- Copied from 1st CASE
      AND @fechaFin <= CreationDate   -- Copied from 2nd CASE

   -- gets results from @fechaInicio to @fechaFin
   -- i.e. results from 04/05/2016  to 04/16/2016

IF @periodo = '1' THEN
    WHERE DATEPART(MONTH,CreationDate) >= @Fecha   -- Copied from 1st CASE
      AND @Fecha <= DATEPART(MONTH,CreationDate)   -- Copied from 2nd CASE

    -- In these case both conditions are the same 'cause 
    -- @Fecha is the number of a month (1 - 12)
    -- i.e. @Fecha = 3 will get all the results of March
    -- regardless of what it is on @fechaInicio and @fechaFin

IF @periodo = '2' THEN
    IF @fetcha = 13 THEN
        WHERE DATEPART(MONTH,CreationDate)>= 1  -- Copied from 1st CASE
          AND 6<=DATEPART(MONTH,CreationDate)   -- Copied from 2nd CASE
    IF @fetcha = 14 THEN
        WHERE DATEPART(MONTH,CreationDate)>= 7  -- Copied from 1st CASE
          AND 12<=DATEPART(MONTH,CreationDate)  -- Copied from 2nd CASE

    -- You never use @fetchaInicio?
    -- You want everything in the first 6 months or last 6 months
    -- For every year
    -- Regardless of what is in @fetchaInicio?

    -- Exactly!!--

IF @periodo = '3' THEN
    IF @fetcha = 15 THEN
        WHERE DATEPART(Year,CreationDate)>= 2015  -- Copied from 1st CASE
          AND 2015 <= DATEPART(Year,CreationDate) -- Copied from 2nd CASE

    --

And what about the case @periodo = '2' AND @fetcha NOT IN (13,14)? And what about the case @periodo = '3' AND @fetcha NOT IN (15)?

This case would not exist, its restricted on the client side. If they chose @Periodo = '2' then @Fecha will have values of 1 - 12 and nothing else.

Same with @Periodo = '3' then @Fecha will have values of 15 or 16, both refering to 2015 or 2016.

Possible duplicate of [Conditional where clause in Sql Server?](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Apr 20 '16 at 15:35
`AND @VPeriodo >= @VFecha AND @VPeriodo <= @VFecha` huh??.. can you be more specific about what your problem is? — JamieD77, Apr 20 '16 at 15:39
@JamieD77 Im getting the results between a time frame (`@fechaInicio` and `@fechaFin`) However uppon the user request (using the `@Periodo` parameter), I need to get the results from that TimeFrame but it could be by month, semester or year, in `@VPeriodo` I'm storing the Time Lapse desired to compare (`DATEPART`) in `@VFecha` its the Time frame that I need (i.e. if its months its 1 - 12) — abichango, Apr 20 '16 at 15:49
@abichango - If you can give real examples in your question I may be able to help more today. Don't try using pseudo code that looks like SQL but with invalid syntax, that just causes confusion. Give actual sample data and actual input values, then actual output to demonstrate what should happen in each case. http://stackoverflow.com/help/mcve — MatBailie, Apr 21 '16 at 08:03
@abichango - Just to double check, because it is such an unusual use-case, when `@periodo` is `1` or `2`, then you want results from Every year in the database? You never want `March-2015`, you only ever want `March`? Even if/when you have 10 years of data in there? — MatBailie, Apr 21 '16 at 15:09
@MatBailie I do came to that question as well yesterday, and I have sorted it out, I just need to add another filter of year for those cases, but to not complicate this even more (sorry for all the confusion) lets assume i do want all the march's available. — abichango, Apr 21 '16 at 16:03

MatBailie · Answer 1 · 2016-04-21T08:29:35.577

You're much better off re-organising the WHERE clause, such that the filtered field is on the left had side and not inside any functions.

For example...

WHERE
      CreationDate >= @VPeriodo
  AND CreationDate <  CASE
                        WHEN @Periodo = '0' THEN DATEADD(DAY,   1, @VPeriodo)
                        WHEN @Periodo = '1' THEN DATEADD(MONTH, 1, @VPeriodo)
                        WHEN @Periodo = '2' THEN DATEADD(MONTH, 1, @VPeriodo)
                        WHEN @Periodo = '3' THEN DATEADD(YEAR,  1, @VPeriodo)
                      END

In this example the right hand side is all scalar constants. This means that you can then do a range scan on the CreationDate field.

Also, @VPeriodo should be a DATE or DATETIME rather than a VARCHAR(MAX).

EDIT: Including hoops to jump through for using VARCHARs

All date's will need to be in the format YYYYMMDD when using VARCHAR. This is so that the natrual order of the stirngs is the same as the natural order of the dates...
- '20161101' > '20161002'

When using other formats, such as YYYYDDMM, it fails...
- '20160111' < '20160210' Problem, in this format 2nd Oct comes AFTER 1st Nov

WHERE
      CreationDate >= @VPeriodo
  AND CreationDate <  CONVERT(
                        NVARCHAR(8),
                        CASE
                          WHEN @Periodo = '0' THEN DATEADD(DAY,   1, CAST(@VPeriodo AS DATE))
                          WHEN @Periodo = '1' THEN DATEADD(MONTH, 1, CAST(@VPeriodo AS DATE))
                          WHEN @Periodo = '2' THEN DATEADD(MONTH, 1, CAST(@VPeriodo AS DATE))
                          WHEN @Periodo = '3' THEN DATEADD(YEAR,  1, CAST(@VPeriodo AS DATE))
                        END,
                        112    -- Format code for ISO dates, YYYYMMDD
                      )

EDIT: A question to the OP after the OP made comments and altered the question

Here all I have done is re-arrange your code to make pseudo-code for what you've written...

IF @periodo = '0' THEN
    WHERE CreationDate  >= @fetchaInicio  -- Copied from 1st CASE
      AND @fetchaInicio <= CreationDate   -- Copied from 2nd CASE

    -- These two conditions are direct from your code
    -- But they're the same as each other
    -- What do you REALLY want to happen when @Periodo = '0'?

IF @periodo = '1' THEN
    WHERE DATEPART(MONTH,CreationDate) >= @Fecha   -- Copied from 1st CASE
      AND @Fecha <= DATEPART(MONTH,CreationDate)   -- Copied from 2nd CASE

    -- These two conditions are direct from your code
    -- But they're the same as each other
    -- What do you REALLY want to happen when @Periodo = '1'?

IF @periodo = '2' THEN
    IF @fetcha = 13 THEN
        WHERE DATEPART(MONTH,CreationDate)>= 1  -- Copied from 1st CASE
          AND 6<=DATEPART(MONTH,CreationDate)   -- Copied from 2nd CASE
    IF @fetcha = 14 THEN
        WHERE DATEPART(MONTH,CreationDate)>= 7  -- Copied from 1st CASE
          AND 12<=DATEPART(MONTH,CreationDate)  -- Copied from 2nd CASE

    -- You never use @fetchaInicio?
    -- You want everything in the first 6 months or last 6 months
    -- For every year
    -- Regardless of what is in @fetchaInicio?

IF @periodo = '3' THEN
    IF @fetcha = 15 THEN
        WHERE DATEPART(Year,CreationDate)>= 2015  -- Copied from 1st CASE
          AND DATEPART(Year,CreationDate)>= 2015  -- Copied from 2nd CASE

    -- Both conditions are the same again, why?
    -- You want everything from 2015 onwards, forever?
    -- You never use @fetchaInicio?
    -- It's always 2015?

And what about the case @periodo = '2' AND @fetcha NOT IN (13,14)? And what about the case @periodo = '3' AND @fetcha NOT IN (15)?

Please could you take my pseudo-code above and give some real examples of what you actually want to do in each case?

the thing is `@VPeriodo` is not an actual parameter, i used it tho "simulate" a variable to equalize it to some value(obviously doesnt work like that). Its also `VARCHAR` because the `CreationDate` its laso `VARCHAR` (Thats how someone designed the table) — abichango, Apr 20 '16 at 15:57
@abichango - I don't understand, your function definition has a variable called `@vperiodo`. But regardless of it being a variable, or a field from another table *(being used in a join predicate)*, this structure still holds. Do you have an *actual* example where you are unable to apply this strucutre? — MatBailie, Apr 20 '16 at 16:06
I think I wasn't clear enough (sorry), I do have the `@VPeriodo` variable, but its not in any other table, neither it has a value itself, I tried to use it as a local variable, where i could make its value equal to `CreationDate` or `DATEPART(month,CreationDate`), not to compare it (as SQL does) to those variables. The original query doesnt have the @VPeriodo, neither the @VFecha and @VFecha 2, like i said i tried to use them as variables to save data on them, not to compare data with them, does that make sense?? Also this is the actual query am working with. — abichango, Apr 20 '16 at 16:11
@abichango - No, sorry, that doesn't make sense to me. How about you give an example of what you're *actually* going to do, in your question? — MatBailie, Apr 20 '16 at 16:13
@abichango - Why do you believe that an in-line table valued function can't have parameters? And/or why do you believe that you can't call this iTVF with such a parameter, as chosen by a user from a VBA form? — MatBailie, Apr 20 '16 at 16:21
sorry for the trouble, probably I'm still not explaining myself, however i just edited the code on the question, that's what I want to achieve — abichango, Apr 20 '16 at 16:27
@abichango - You're thinking about everything from the wrong direction. You want something like `CreationDate >= [A BIG CASE STATEMENT] AND CreationDate < [ANOTHER BIG CASE STATEMENT]` If you can do that, you're sorted. I'm leaving for home now, best I can do is reply tomorrow. But if you look at my answer (although not directly related to your needs) and what you had originally, hopefully that may steer you to think about things in a different way. — MatBailie, Apr 20 '16 at 16:30
Thanks a lot for your help, I do understand the thing about having the filtered field on the left side, and your statement about the `CreationDate >= [A BIG CASE STATEMENT] AND CreationDate < [ANOTHER BIG CASE STATEMENT]` its correct, thats what I need and I do have an idea on how to achieve that, my problem its that not always I will compare the whole `CreationDate` field, in some cases I need to compare only the month or the year of `CreationDate`. So how can I make the query to use the whole `CreationDate` or just the month or Year of such field to compare with the Case statement result. — abichango, Apr 20 '16 at 17:15
@abichango - look at how I did it in my answer. If you need one month, then you need dates from the start of the month, but less than the start of the next month. Same applies to weeks, years, everything. And even if you don't know the start of the day/week/month/year, there are hundreds of SO posts on rounding down to a day/week/month/year... — MatBailie, Apr 20 '16 at 21:10
just edited the question with your pseudo-code, I also answered your questions there. — abichango, Apr 21 '16 at 14:37

score 0 · Answer 2 · answered Apr 20 '16 at 15:47

0

Whenever you want to use parameters as conditionals in your where clause, you can follow this format:

WHERE 
((@Periodo = '0' AND CreationDate
OR (@Periodo = '1' AND @VPeriodo = CAST(DATEPART(MONTH,CreationDate) as Nvarchar(10))
OR (@Periodo = '2' AND @VPeriodo = CAST(DATEPART(MONTH,CreationDate) as Nvarchar(10))
OR (@Periodo = '3' AND @VPeriodo = CAST(DATEPART(Year,CreationDate) as Nvarchar(10)))

Make sure the parameter evaluation is first because that will be fast and it being false will stop the query from continuing to process the more processing intensive part of the condition.

answered Apr 20 '16 at 15:47

Langosta

487
3
16

That's still notoriously slow. Multiple OR conditions on their own can throw the optimiser out. And embedding the search field in a function is often irretrievable. Both yielding full-table-scans rather than index-range-seeks. – MatBailie Apr 20 '16 at 15:52
@MatBailie Someone marked my question as a possible duplicate of this [question](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server)! And the answer is the same as this one, I think it solves my issue, however due to the speed issue, is there any other way to achieve this?? – abichango Apr 20 '16 at 16:03
@abichango - Here is a detailed link as to why this type of query performs poorly. http://www.sommarskog.se/dyn-search-2008.html As for a specific alternative, I'm uncertain as to exactly what you mean in your comment on my answer. – MatBailie Apr 20 '16 at 16:10

score 0 · Accepted Answer · answered Apr 20 '16 at 16:06

First, you are correct, inline TFV is really faster. If not overcomplicated. I'd better have a number of iTFVs for each @Periodo parameter value on the SQL Server side and choose the right one in code on the client side.

Alternatively you may do it in a single iTVF

WHERE 
  @Periodo = '0' AND  CreationDate>= @fechaInicio and CreationDate<=@fechaFin
  OR @Periodo = '1' and DATEPART(MONTH,CreationDate)>= @Fecha and DATEPART(MONTH,CreationDate)<= @Fecha
  ...

But MS SQL is known to build ocassionally bad plans for OR operators which may render your efforts to stick to iTVF useless.

and your last statement is the issue, its a growing table, how many OR's would be a problem?? or it's more about the `OR` statement rather than how many `OR` there are?? — abichango, Apr 20 '16 at 16:29

CASE inside WHERE Clause

3 Answers3