CODEWITHSUNDEEP
sqlsql-server
user667430
user667430

Reputation: 1547

SQL Server Case in Where clause

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

Upvotes: 1

Views: 121

Answers (3)

Alain Bates
Alain Bates

Reputation: 466

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))))

Upvotes: 1

Hiren gardhariya
Hiren gardhariya

Reputation: 1247

You can use something like this,

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

Upvotes: 0

Veera
Veera

Reputation: 3492

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)))

Upvotes: 1

Related Questions