SQL SP - CASE in WHERE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL SP - CASE in WHERE clause

Author

Topic

BigJohnson
Starting Member

9 Posts

Posted - 2006-10-24 : 11:09:26

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
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 11:14:20

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

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 11:18:08

or to be simpler start with IF ELSE

IF codintion
query with where1
ELSE
query with where2

Madhivanan

Failing to plan is Planning to fail

BigJohnson
Starting Member

9 Posts

Posted - 2006-10-24 : 11:30:10

Thats perfect - thanks for that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources