help with query conditions - SQL Server Forums

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)

help with query conditions

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-08 : 02:24:16

Hi,

I have the following query I am trying to modify, ive marked it below between the ***'s.

Basically if I pass a "1" or "2" value via @showMe_joinedFromMyArea I want to run a filter against genderID (a value of 3 means don't filter, a value of 0 means bring back 0 results)

Any help greatly appreciated.. I'm not sure how I can approach this without dynamic sql, but I am sure there is a way...

thanks again!
mike123

UNION ALL

SELECT message1 AS msg,

date as theDate,
15 AS theType,

userID as block_userID,
nameOnline as block_nameOnline

FROM
tblUserDetails WITH (NOLOCK) WHERE active = 1 AND stateProvID=@stateProvID

--***************************
--this line below, if @showMe_joinedFromMyArea = 1 or @showMe_joinedFromMyArea = 2 I want to filter the genderID,
--and genderID = @showMe_joinedFromMyArea
--******************************************

AND @showMe_joinedFromMyArea IN (1,2,3)
AND userID NOT IN (SELECT [blocked_userID] FROM @blockedUsers)

--merge with above queries
UNION ALL

matty
Posting Yak Master

161 Posts

Posted - 2009-04-08 : 02:43:16

and genderID = CASE @showMe_joinedFromMyArea WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN genderID END

Kokkula
Starting Member

41 Posts

Posted - 2009-04-08 : 03:12:47

Hello,

Try this code

UNION ALL

SELECT message1 AS msg,

date as theDate,
15 AS theType,

userID as block_userID,
nameOnline as block_nameOnline

FROM
tblUserDetails WITH (NOLOCK)
WHERE active = 1
AND stateProvID=@stateProvID
AND @showMe_joinedFromMyArea = CASE WHEN @showMe_joinedFromMyArea IN (1,2) THEN GnederID ELSE @showMe_joinedFromMyArea END

--***************************
--this line below, if @showMe_joinedFromMyArea = 1 or @showMe_joinedFromMyArea = 2 I want to filter the genderID,
--and genderID = @showMe_joinedFromMyArea
--******************************************

AND @showMe_joinedFromMyArea IN (1,2,3)
AND userID NOT IN (SELECT [blocked_userID] FROM @blockedUsers)

--merge with above queries
UNION ALL

Thanks,
Pavan

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-08 : 03:56:44

kokkula,

awesome, thank you very much!! works perfectly

cheers,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 04:33:39

AND GenderID = COALESCE(NULLIF(@showMe_joinedFromMyArea, 3), GenderID)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 04:35:53

If GenderID only have 1 and 2 as values, try this

AND	(
		GenderID = @showMe_joinedFromMyArea
		OR @showMe_joinedFromMyArea = 3
	)

This will also fix the problem with @showMe_joinedFromMyArea = 0 where you stated you wanted zero record back.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources