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)
 Where clause using CASE?

Author  Topic 

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-25 : 13:56:32
I need help with using a CASE statement in the where clause for my parameter. What I'm trying to do is only have specific data to be use as the parameter. When the user selects the parameter it will display the data base on that. The problem, I'm having is that if the user selects 'ALL' to see all, it will pull everything in that field and I just want what is listed in my drop down list for the report.

Example of a simple query:


SELECT F.NAME, F.DEPARTMENT, C.COURSE, C.SCHEDULE
FROM FACULTY F LEFT OUTER JOIN CLASS C
F.ID = C.FACULTY_ID
WHERE F.NAME LIKE CASE @NAME
WHEN 'JOHNSON' THEN 'JOHNSON'
WHEN 'JOHN' THEN 'JOHN'
WHEN 'MARK' THEN 'MARK'
WHEN 'YANG' THEN 'YANG'
WHEN 'ALL' THEN F.NAME IN
('JOHNSON', 'JOHN', 'MARK' ,'YANG')
END


If I use
WHEN 'ALL'	THEN F.NAME
it will pull other names from the database and I only want for those specific names. If I use the example above it gives me an error at "IN". Not sure if there's a way to only include those names in there.

Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-25 : 14:32:11
The result of a CASE Statement must be a single value, you'll need 2 statements

IF @Name = 'ALL'
SELECT QUERY WHERE Name ON ('...')
ELSE
SELECT QUERY WHERE Name = @Name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-25 : 14:37:01
Thanks for the example. Also got it to work with a nested select statement.


SELECT F.ID, FACULTY_NAME.NAME, F.DEPARTMENT, C.COURSE, C.SCHEDULE
FROM FACULTY F LEFT OUTER JOIN CLASS C
F.ID = C.FACULTY_ID

LEFT OUTER JOIN
(SELECT F.NAME
FROM FACULTY F
WHERE F.NAME IN ('JOHNSON', 'JOHN', 'MARK' ,'YANG')) FACULTY_NAME ON
F.ID = FACULTY_NAME.ID

WHERE F.NAME LIKE CASE @NAME
WHEN 'JOHNSON' THEN 'JOHNSON'
WHEN 'JOHN' THEN 'JOHN'
WHEN 'MARK' THEN 'MARK'
WHEN 'YANG' THEN 'YANG'
WHEN 'ALL' THEN FACULTY_NAME.NAME

END
Go to Top of Page

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-25 : 14:45:14
One more variant without CASE:

SELECT F.NAME, F.DEPARTMENT, C.COURSE, C.SCHEDULE
FROM FACULTY F LEFT OUTER JOIN CLASS C
F.ID = C.FACULTY_ID
WHERE F.NAME IN (
SELECT @NAME WHERE @NAME IN('JOHNSON', 'JOHN', 'MARK' ,'YANG') UNION
SELECT 'JOHNSON' WHERE @NAME = 'ALL' UNION
SELECT 'JOHN' WHERE @NAME = 'ALL' UNION
SELECT 'MARK' WHERE @NAME = 'ALL' UNION
SELECT 'YANG' WHERE @NAME = 'ALL'
)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-25 : 16:30:00
Why are the names explicitly listed out in the SELECT? Are there other names in this table that should always be excluded? what happens if more names are added? I would store this logic in a table, either by adding a columm to your table of names to indicate who can access these reports, or creating a new table to list those that can. This makes maintaining your system a matter of editing DATA as opposed to editing SQL statements all over the place ...

VladRUS.ca -- that's a clever one, I'd had never seen that done before. Very original !

If you want to allow for the name to match either the name passed in, or only a hard-coded list of names, then do it simply and logically:

WHERE NAME = @Name OR
(@Name = 'ALL' AND Name IN('JOHNSON', 'JOHN', 'MARK' ,'YANG'))

Doesn't that make a little more sense, and isn't it a little more readable?
Go to Top of Page

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-25 : 16:53:38
WHERE NAME = @Name OR
(@Name = 'ALL' AND Name IN('JOHNSON', 'JOHN', 'MARK' ,'YANG'))

To jsmith8858: Cool! Your solution generates better plan and should be faster!


Go to Top of Page
   

- Advertisement -