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.

Answer 1

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?

Answer 2

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.

Answer 3

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.