SQL - Case in a WHERE clause

[BigJohnson]

Pulling my hair out with this one. I'm attempting to place a CASE statement within a WHERE clause so that the query becomes a little dynamic.

With the case removed, the clause works fine, with the CASE statement in place, it fails with a syntax error at the "NOT" part on the first line.

Any ideas?
Thanks.


WHERE (tblDefect.Status <> 'closed') AND (tblDevCom.Dev_Id = @DevID) AND (tblSites.SiteID LIKE @TEMP OR @TEMP IS NULL) AND
(
CASE @EoD
WHEN 'A' THEN (tblDefStd.DefStdDesc NOT LIKE '%End%')
WHEN 'B' THEN (tblDefStd.DefStdDesc LIKE '%End%')
END
)

[russell]

if u are using Stored Procedures, this can be a lot more elegant (and perform better) but this will work

Declare @EoD char(1)
Set @EoD = 'A'

IF @EoD = 'A'
Begin
SELECT fieldList
FROM tables
WHERE tblDefect.Status <> 'closed'
AND tblDevCom.Dev_Id = @DevID
AND (tblSites.SiteID LIKE @TEMP OR @TEMP IS NULL)
And tblDefStd.DefStdDesc NOT LIKE '%End%'
End
Else
Begin
SELECT fieldList
FROM tables
WHERE tblDefect.Status <> 'closed'
AND tblDevCom.Dev_Id = @DevID
AND (tblSites.SiteID LIKE @TEMP OR @TEMP IS NULL)
And tblDefStd.DefStdDesc LIKE '%End%'
End

[mattyblah]

I can't think of a situation where you would need a case in a where clause. Try this:

WHERE (tblDefect.Status <> 'closed') AND (tblDevCom.Dev_Id = @DevID) AND (tblSites.SiteID LIKE @TEMP OR @TEMP IS NULL) AND
(((@EoD = 'A') AND (tblDefStd.DefStdDesc NOT LIKE '%End%')) OR
((@EoD='B') AND (tblDefStd.DefStdDesc LIKE '%End%')))