Limiting results using IF ELSE

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 2005 Forums

Transact-SQL (2005)

Limiting results using IF ELSE

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-05-24 : 05:02:54

I'm trying to write a query where if the value of one of the paramters is equal to "-1" it will not include one of the search criteria. For example I have a param called "Gender". 0 = Male and 1 = Female. If the value of the "Gender" param is "-1" I'd like it to find either Males or Females.

Code (please not this doesn't compile):

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
IF (@Gender != -1)
AND Gender = @Gender

Obviously this doesn't work, but I think it conveys the gist of what I'm trying to do. Does the secret lie in default parameters? Or is my IF syntax just flat out wrong?

Thanks!

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-24 : 07:21:08

quote:
Originally posted by shawnmolloy

I'm trying to write a query where if the value of one of the paramters is equal to "-1" it will not include one of the search criteria. For example I have a param called "Gender". 0 = Male and 1 = Female. If the value of the "Gender" param is "-1" I'd like it to find either Males or Females.

Code (please not this doesn't compile):

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
IF (@Gender != -1)
AND Gender = @Gender

Obviously this doesn't work, but I think it conveys the gist of what I'm trying to do. Does the secret lie in default parameters? Or is my IF syntax just flat out wrong?

Thanks!

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
and @Gender != -1
AND Gender = @Gender

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
AND Gender = @Gender
AND @Gender IN ( 0,1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 07:29:19

quote:
Originally posted by raky

quote:
Originally posted by shawnmolloy

I'm trying to write a query where if the value of one of the paramters is equal to "-1" it will not include one of the search criteria. For example I have a param called "Gender". 0 = Male and 1 = Female. If the value of the "Gender" param is "-1" I'd like it to find either Males or Females.

Code (please not this doesn't compile):

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
IF (@Gender != -1)
AND Gender = @Gender

Obviously this doesn't work, but I think it conveys the gist of what I'm trying to do. Does the secret lie in default parameters? Or is my IF syntax just flat out wrong?

Thanks!

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
and @Gender != -1
AND Gender = @Gender

SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
AND Gender = @Gender
AND @Gender IN ( 0,1)

Both these conditions wont return anything when value of @Gender is -1.

It should be like this

SELECT 
[UserID],[Gender]
FROM
[table_user] 
WHERE
Country = @Country 
AND IsOnline = @IsOnline
AND (Gender = @Gender OR @Gender=-1)

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-05-25 : 16:17:22

quote:
AND (Gender = @Gender OR @Gender=-1)

Quite brilliant in its simplicity! I love it.

Thanks Raky.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources