CASE statement in WHERE clause

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)

CASE statement in WHERE clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-21 : 08:06:28

Aditi writes "I am trying to use the CASE statement in the WHERE clause on the following lines, but it gives me a syntax error. Would like to know a work-around method. Thanks in advance.

(I have a stored procedure which accepts 2 input parameters - @SearchBy and @SearchValue)

SELECT *
FROM tblMembers
WHERE
CASE WHEN @SearchBy = 1 THEN MemberID = @SeachValue
ELSE
CASE WHEN @SearchBy = 2 THEN FirmName = @SeachValue
ELSE
CASE WHEN @SearchBy = 3 THEN Telephone = @SeachValue
ELSE
NULL
END
END
END"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2003-04-21 : 08:17:22

You almost have it.

SELECT *
FROM tblMembers
WHERE
CASE @SearchBy
WHEN 1 THEN MemberID = @SeachValue
WHEN 2 THEN FirmName = @SeachValue
WHEN 3 THEN Telephone = @SeachValue
END

Jeremy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 16:58:07

Jeremy --
does what you posted actually work? A CASE function only returns a value, and SQL cannot return boolean expressions.

I think you need to do it this way:

SELECT *
FROM tblMembers
WHERE
(@SearchBy <> 1 OR MemberID = @SearchValue) AND
(@SearchBy <> 2 OR FirmName = @SeachValue) AND
(@SearchBy <> 3 OR Telephone = @SeachValue)

Not sure how efficient that will end up being, however.

In boolean logic, to indicate an "IF-THEN" you need to use Not-OR.
In other words, to code:

IF (A is true) then (B must be true)

you turn it into

(Not A) or B

A useful trick for complex WHERE clauses.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources