How to use CASE statement when to local variables for multiple conditions

Question

on basis of local variable value I want to make condition on a particular column in select statement.

declare @str VARCHAR(50)='ProjectCostAll'

SELECT pm.intProjectID,pm.strProjectName,im.intInvID,im.strInvNo 
FROM dbo.tblProjectMaster PM
LEFT JOIN dbo.tblInvoiceDetails ID ON id.intProjectID=pm.intProjectID
LEFT JOIN dbo.tblInvoiceMaster IM ON im.intInvID=id.intInvID
WHERE CASE 
    WHEN @str ='ProjectCostInv' THEN 
        ISNULL(IM.intInvID,0) <> 0
    WHEN @str ='ProjectCostNotInv' THEN 
        ISNULL(IM.intInvID,0) = 0
    ELSE
        ISNULL(IM.intInvID,0) >= 0
    end

Possible duplicate of ["CASE" statement within "WHERE" clause in SQL Server 2008](https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008) — SMor, Mar 03 '18 at 14:06

score 2 · Accepted Answer · answered Mar 03 '18 at 09:23

WHERE (@str = 'ProjectCostInv' AND IM.intInvID <> 0)
    OR (@str = 'ProjectCostNotInv' AND ISNULL(IM.intInvID,0) = 0)
    OR (@str not in ('ProjectCostInv', 'ProjectCostNotInv') AND ISNULL(IM.intInvID,0) >= 0)

but the overall approach is bad for performance.

score 0 · Answer 2 · answered Mar 03 '18 at 09:24

I don't eval the meaning of your case result but out of this if you want see the result in select you should use the case in select not in where

SELECT pm.intProjectID,pm.strProjectName,im.intInvID,im.strInvNo,
    CASE 
        WHEN @str ='ProjectCostInv' THEN '<> 0'
        WHEN @str ='ProjectCostNotInv' THEN '0'
        ELSE '>= 0'
        end my_result 
FROM dbo.tblProjectMaster PM
LEFT  JOIN dbo.tblInvoiceDetails ID ON id.intProjectID=pm.intProjectID
left JOIN dbo.tblInvoiceMaster IM ON im.intInvID=id.intInvID

How to use CASE statement when to local variables for multiple conditions

2 Answers2