Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!