How to use case in SQL query

Question

1.00/5 (1 vote)

See more:

I have a table test with name varchar(20),id int & gender char(1) columns.I want to write the condition using gender.

If gender=0 then show all gender details
gender=1 then male
gender=2 then female

This query is not showing any detail..can any one give suggestion.

Thank you.

What I have tried:

SQL

select name,gender from test where case when @Gender=0 then 1 else a.Gender end =case when @Gender=0 then 1 else @Gender end

Posted 26-Feb-17 21:53pm

Developer29

Updated 27-Feb-17 1:37am

CPallini

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-02-26T22:09:00

Solution 1

Don't use CASE in a WHILE, use AND and OR instead. Assuming a.Gender is also 1 for male and 2 for female, try:

SQL

... WHERE @Gender = 0 OR @GENDER = a.Gender

Posted 26-Feb-17 22:09pm

OriginalGriff

Sahan2u · Answer 2 · 2017-02-27T00:26:00

Solution 2

This is simple. Try following query. (I don't really understand what you want to display when the gender = 0. So in my query I display blank when gender is 0)

SQL

select name,
	case when gender = '0' then ''
		when gender = '1' then 'male'
		when gender = '2' then 'female' end as gender
from test

Posted 27-Feb-17 0:26am

Sahan2u

Comments

CHill60 27-Feb-17 10:40am

When gender = 0 the OP wants to include both male and female records, if gender = 1 then they want to display only male records and if gender = 2 only female records. Your solution does not do this - it only converts the number to a varchar.

Vinay_kr0164 · Answer 3 · 2017-02-27T01:37:00

Solution 3

declare @Gender int

select name,gender, case when @Gender = 0
then (select * from test
where Gender in ('Male','Female') )
when @Gender = 1 then (select * from test
where Gender = 'Male')
when @Gender = 2 then (select * from test
where Gender = 'Female')
end
from test

Posted 27-Feb-17 1:37am

Vinay_kr0164

Comments

CHill60 27-Feb-17 10:44am

This is syntactically incorrect - it won't even run without producing errors
Msg 116, Level 16, State 1, Line 6
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 8
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 10
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.