CODEWITHSUNDEEP
sqlsql-server
GROVER_SYAAN
GROVER_SYAAN

Reputation: 365

sql server using where with if else condition

i have store procedure which is receiving gender value that could be male, female or both, i need to be able to condition my where clause so that if the @gender value is 'both' then select should return male and female, but if gender is either male or female then then @gender value should be selected. i tried below query both it is giving incorrect syntax error for where.

select FirstName,SecondName,Gender,race,Height,HeightUnit, Weight,WeightUnit,EmailAdd from volunteers 

IF @gender = 'both'
BEGIN

    WHERE Gender = 'male' or Gender ='female' 

END ELSE BEGIN

    WHERE Gender = @gender
END

Upvotes: 0

Views: 689

Answers (2)

logixologist
logixologist

Reputation: 3834

Ok If I am understanding you correctly... try it this way:

select FirstName,SecondName,
CASE When @Gender = 'both' then 'Male, Female' else @Gender as Gender  END
,race,Height,HeightUnit, Weight,WeightUnit,EmailAdd from volunteers

You can tweak exactly what you want it to say but this is the idea

UPDATE: per the comments this is the full procedure that he is using:

    Create PROCEDURE spSelectUpdateTrial 

    @gender nvarchar(50), 
    @minage nvarchar(150), 
    @maxage nvarchar(150), 
    @country nvarchar(50), 
    @smoking nvarchar (50), 
    @smoke nvarchar(50), 
    @nct nvarchar(50) AS 

    select FirstName,
SecondName,
CASE 
When @Gender = 'both' 
then 'Male, Female' 
else @Gender
END 
as 
Gender,
race,
Height,
HeightUnit,
Weight,
WeightUnit,
EmailAdd 
from volunteers

Note I added an END after else @gender

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You cannot use an if to have a conditional where clause. Instead, just put the logic in the query:

Based on the OP, you want:

select FirstName, SecondName, Gender, race, Height, HeightUnit,
       Weight, WeightUnit, EmailAdd
from volunteers
where @gender = 'both' and gender in ('male', 'female') or
      gender = @gender;

Alternatively, this might be sufficient:

select FirstName, SecondName, Gender, race, Height, HeightUnit,
       Weight, WeightUnit, EmailAdd
from volunteers
where @gender = 'both' or
      gender = @gender;

Upvotes: 2

Related Questions