SQL Conditions?

Xyis · July 22nd, 2008, 06:51 AM

Hi there,

I have this SQL

SELECT tblUserData.*
FROM tblUserData
ORDER BY rank DESC

and i would like to pass into it a gender, for example.

SELECT tblUserData.*
FROM tblUserData
WHERE sex = @Gender
ORDER BY rank DESC

The trouble is @Gender can equal one of three things, "male", "female" or "both". This works fine for male and female but how can i get it to return both male AND female if i am passing in "both"?

Thanks,
Xyis.

stu2000 · July 22nd, 2008, 07:04 AM

you could use an if statement,

Code:

if @Gender == 'both'
query = SELECT tblUserData.* FROM tblUserData ORDER BY rank DESC
else
query = SELECT tblUserData.* FROM tblUserData WHERE sex = @Gender ORDER BY rank DESC

or something along those lines, depends what code (PHP, ASP etc) you are using on your site as to exactly how you code it into the page.

would that work for what you want to do?

Xyis · July 23rd, 2008, 05:41 AM

Sorry mate, this aint working for me. Says it's "unable to parse query text". I tried to correct the error myself but my knowledge of SQL is limited, hence me being here

.

I am using an ASP.NET typed dataset to make a table adapter method GetPeople(@Gender) which gets all the people in the database based off the value of Gender (male, female, both)

welshstew · July 23rd, 2008, 05:57 AM

Try the following:

Code:

SELECT tblUserData.* FROM tblUserData WHERE
(Gender='Male' OR Gender='Female' OR Gender='Both')
ORDER BY rank DESC

Xyis · July 23rd, 2008, 06:10 AM

Thanks for the reply, I don't need to select one of 3 values, i need to select data based on the value of @Gender.

If @gender = 'male' I want to select all the male people.
If @gender = 'female' I want to select all the female people.
and If @gender = 'both' I want to select all the people (male and female)

I'm starting to think about just having two separate queries and getting my code to work out which one to use.

Xyis · July 23rd, 2008, 07:05 AM

Used a stored procedure based off the original reply, is now working.

Thanks.

(@Gender NVARCHAR(7))

AS
if @Gender = 'both'
SELECT tblUserData.* FROM tblUserData ORDER BY rank DESC
else
SELECT tblUserData.* FROM tblUserData WHERE sex = @Gender ORDER BY rank DESC
GO

stu2000 · July 23rd, 2008, 12:53 PM

no problems, sorry I don't code ASP so wouldn't have been able to give you the exact ASP code, but glad my idea made sense... kind of lol!