SQL case else problem - 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 case else problem

Author

Topic

melberti
Starting Member

10 Posts

Posted - 2005-04-08 : 09:42:10

I have a case statement in my where clause. I'm querying a table for people of a certain gender. When one thing is true, I want only women, when another, I want only men. But in the else case, I want people of both genders. I can't seem to accomplish this else part. Any ideas?

Thanks.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-04-08 : 09:46:57

how about some data and desired output..?

melberti
Starting Member

10 Posts

Posted - 2005-04-08 : 09:55:34

WHERE GENDER =
CASE
WHEN @p_SEARCH = 'F' then 'F' -- only get women
WHEN @p_SEARCH = 'M' then 'M' -- only get men
-- ELSE ??? -- get either M/F, but HOW??
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 09:57:30

declare @gender varchar(10)
-- comment both for all so @gender is null
set @gender = 'M' -- for men
--set @gender = 'F' -- for women

select * from MyTable
where (gender = @gender or @gender is null)

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 09:58:35

for you specific case:
WHERE GENDER =
CASE
WHEN @p_SEARCH = 'F' then 'F' -- only get women
WHEN @p_SEARCH = 'M' then 'M' -- only get men
ELSE GENDER
END

but my first reply is better for what you need.

Go with the flow & have fun! Else fight the flow

melberti
Starting Member

10 Posts

Posted - 2005-04-08 : 10:07:54

Ha! So simple! ELSE GENDER worked perfectly.
Thank you very much.

Melberti

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources