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)

CASE in WHERE CLAUSE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-03 : 07:49:52

Brendon writes "Hi,

Is it possible to use the CASE statement in a WHERE CLAUSE.

Ex:
Create Proc MyProc(@Param1 int,@Param2 varchar(50))
AS

SELECT * FROM CUSTOMERS
CASE @Param2
WHEN @Param2 IS NULL THEN WHERE CustomerID = @Param1
ELSE WHERE CustomerName LIKE '%' + @Param2 + '%'
END

Regards

Brendon"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-03 : 07:53:23

Yes you can, but your example doesn't actually need CASE:

Create Proc MyProc(@Param1 int,@Param2 varchar(50)) AS
set nocount on
SELECT * FROM Customers
WHERE (@param2 IS NULL AND CustomerID=@param1)
OR CustomerName LIKE '%' + @param2 + '%'

The thing to remember about CASE is that it works like a function, not a statement. It returns a value. This syntax is incorrect in SQL:

CASE @Param2 WHEN @Param2 IS NULL THEN WHERE CustomerID = @Param1

...because CASE does not control the flow of commands. This construct IS correct though:

WHERE CustomerID=CASE WHEN @param2 IS Null THEN @param1 ELSE CustomerID END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:59:28

Also check out:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources