How to insert if in where condition SQL

Question

I have this query,

SELECT [Equipment]
      ,[CounterType]      
      ,SUM([Quantity])
FROM [FlexNet].[dbo].[AT_MACHINE_COUNTER]
WHERE 
    CreatedOn IS NOT NULL
    AND [FlexNet].[dbo].AF_GetUTCToLocal(CreatedOn) between @DATA_START and @DATA_END
    AND [Equipment] LIKE @MACHINE
    AND CounterType IN 
    (CASE WHEN @MACHINE LIKE 'PACKER001' THEN 2 END)

I would like to insert this IF in WHERE condition:

IF @MACHINE LIKE 'PACKER001' THEN CounterType = 1
ELSE 'I don't want apply any condition'

So the query should be similar this (but I know that this query not found):

    SELECT [Equipment]
          ,[CounterType]      
          ,SUM([Quantity])
    FROM [FlexNet].[dbo].[AT_MACHINE_COUNTER]
    WHERE 
        CreatedOn IS NOT NULL
        AND [FlexNet].[dbo].AF_GetUTCToLocal(CreatedOn) between @DATA_START and @DATA_END
        AND [Equipment] LIKE @MACHINE
                    IF @MACHINE LIKE 'PACKER001' THEN 
                       AND CounterType =2
                    END IF

Can we help me? reguars

score 3 · Answer 1 · edited May 23 '17 at 11:50

You can build up conditional where clauses by combining both 'branches' with an OR clause like this example, although note that this is fraught with performance and maintainability challenges:

SELECT [Equipment]
      ,[CounterType]      
      ,SUM([Quantity])
FROM [FlexNet].[dbo].[AT_MACHINE_COUNTER]
WHERE 
    CreatedOn IS NOT NULL
    AND [FlexNet].[dbo].AF_GetUTCToLocal(CreatedOn) between @DATA_START and @DATA_END
    AND [Equipment] LIKE @MACHINE
    AND (@MACHINE = 'PACKER001' AND CounterType IN (2)
         OR @MACHINE <> 'PACKER001')

As an alternative to doing this in Sql, I would typically try to manage this kind of dynamic filtering on data in the application layer, e.g. LINQ features such as using IQueryable to conditionally compose filters, or the excellent PredicateBuilder do this much easier.

Pரதீப் · Accepted Answer · 2014-11-27T16:52:27.537

You were almost there in your first attempt. Just add Else part in your Case Statement with CounterType problem solved .

SELECT [Equipment],
       [CounterType],
       Sum([Quantity])
FROM   [FlexNet].[dbo].[AT_MACHINE_COUNTER]
WHERE  CreatedOn IS NOT NULL
       AND [FlexNet].[dbo].Af_getutctolocal(CreatedOn) BETWEEN @DATA_START AND @DATA_END
       AND [Equipment] LIKE @MACHINE
       AND CounterType = CASE
                           WHEN @MACHINE LIKE 'PACKER001' THEN 2
                           ELSE CounterType
                         END

How to insert if in where condition SQL

2 Answers2