SQL Server Case in Where clause

Question

I am trying to create a stored proc and have a where clause where to different operations can take place depending on the value of a parameter passed in:

WHERE
            (cdr.CircuitReference = @CircuitReference)
            AND 
            CASE WHEN (@JDEDocumentReference <> 'Unbilled Calls')
            THEN
                sct.JDEDocumentReference = @JDEDocumentReference
            ELSE
                ((sct.JDEDocumentReference IS NULL) AND (sc.StartDate IS NOT null AND ((sc.CloseDate IS null) OR (datediff(day,sc.CloseDate,getdate()) < 0)) AND stp.SipTrunksProduct = sct.ProductCode))
            END

I've just posted my where clause above but when i try to execute the script i get the following error:

Incorrect syntax near '='.

Is this the correct way to do a conditional statement in a where clause of an sql query?

Thanks

I think it should be like `sct.JDEDocumentReference = CASE WHEN ...` — Bharadwaj, Dec 10 '14 at 11:17
[SQL Switch/Case in where clause](http://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause) — huMpty duMpty, Dec 10 '14 at 11:40

score 1 · Answer 1 · answered Dec 10 '14 at 11:35

STATEMENT FULLY WRONG : There is no need for case here(Even there is a possibility to it correctly. But here no needed).

USE:

 (cdr.CircuitReference = @CircuitReference) 
AND ((JDEDocumentReference <> 'Unbilled Calls' 
     AND @JDEDocumentReference) OR @JDEDocumentReference = 'Unbilled Calls' ) 
OR (JDEDocumentReference = 'Unbilled Calls'
     AND ((sct.JDEDocumentReference IS NULL) AND (sc.StartDate IS NOT null AND ((sc.CloseDate IS null) 
          OR (datediff(day,sc.CloseDate,getdate()) < 0)) AND stp.SipTrunksProduct = sct.ProductCode)))

Hi thanks for replying. I've tried using your suggestion and it says at `AND @JDEDocumentReference` that `An expression of non-boolean type specified in a context where a condition is expected, near ')'.` — user667430, Dec 10 '14 at 11:47

score 1 · Accepted Answer · answered Dec 10 '14 at 11:36

This problem could be solved without a CASE statement by using the following:

WHERE
    (cdr.CircuitReference = @CircuitReference)
AND 
   ((@JDEDocumentReference <> 'Unbilled Calls' AND sct.JDEDocumentReference = @JDEDocumentReference)
   OR    
   (@JDEDocumentReference = 'Unbilled Calls' AND ((sct.JDEDocumentReference IS NULL) AND (sc.StartDate IS NOT null AND ((sc.CloseDate IS null) OR (datediff(day,sc.CloseDate,getdate()) < 0)) AND stp.SipTrunksProduct = sct.ProductCode))))

score 0 · Answer 3 · answered Dec 10 '14 at 11:35

0

You can use something like this,

   WHERE
                (cdr.CircuitReference = @CircuitReference)    
                AND sct.JDEDocumentReference = case when @JDEDocumentReference <> 'Unbilled Calls' Then @JDEDocumentReference end

answered Dec 10 '14 at 11:35

Hiren gardhariya

1,247
10
29

SQL Server Case in Where clause

3 Answers3