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.
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.SCHEDULEFROM FACULTY F LEFT OUTER JOIN CLASS C F.ID = C.FACULTY_IDWHERE 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 statementsIF @Name = 'ALL' SELECT QUERY WHERE Name ON ('...') ELSE SELECT QUERY WHERE Name = @NameBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
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.SCHEDULEFROM 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 |
|
|
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.SCHEDULEFROM FACULTY F LEFT OUTER JOIN CLASS C F.ID = C.FACULTY_IDWHERE 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' ) |
|
|
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? |
|
|
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! |
|
|
|
|
|
|
|