不要在WHILE中使用CASE，请使用AND和OR代替。假设a.Gender对男性为1，女性为2，请尝试：

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

这很简单。尝试以下查询。 （我不太明白当性别= 0时你想要显示什么。所以在我的查询中，当性别为0时我显示空白。）

 选择名称，
案例 当 gender = '  0' 然后 ' ' 
何时 gender = '  1' 然后 '  male' 
何时 gender = '  2' 然后 '  female'  end   as  gender 
来自 test 

声明@Gender int



选择姓名，性别，@Gender = 0时的情况

然后（选择*来自测试>
其中性别（'男'，'女'））

当@Gender = 1然后（选择*来自测试

，其中性别='男性'）

当@Gender = 2然后（选择*来自测试

，其中性别='女性'）

结束
$来自test的b $ b

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:

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

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:

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

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)

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

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