OR Problem - 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)

OR Problem

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 05:14:50

I have a query that I pass 2 parameters into

@Initiated bit
@Received bit

my problems is that my options for my parameters are as simple as true / false, but they can be true / false, true / true, false / true... you get the idea, the one part that messes everything up is that they can also be true OR false...

My query would look like

Select * from table where
Initiated = @Initiated
AND Received = @Received

how can i put something in in case it was OR, I dont mind passing in another parameter stating that OR is to be used, but how would I write the conditional so do the AND's, or the OR ?

Does that make sense?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-07 : 05:17:47

One way is pass null for @initicated or @received

Select * from table where
(Initiated = @Initiated or @Initiated is null)
(AND Received = @Received or @Received is null)

Madhivanan

Failing to plan is Planning to fail

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 06:36:25

I need to be able to query

Select * from table where
Initiated = @Initiated
AND Received = @Received

OR

Select * from table where
Initiated = @Initiated
OR Received = @Received

depending on what parameter I pass in, say that parameter is @method bit, if it is 0 do the AND if it is 1 do the OR... it's not as simple as a case statement because the query itself it much much longer than the generic sample provided, the last part of the query however will be either @Initiated AND @Received, or it will be @Initiated OR @Received

hope that makes better sense

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 07:28:14

try this:
select * from table
where
(@method = 0 and (Initiated = @Initiated and Received = @Received))
or
(@method = 1 and (Initiated = @Initiated or Received = @Received))

No, you're never too old to Yak'n'Roll if you're too young to die.

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 07:38:01

Thank you very much, that did the trick!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 07:55:31

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-07 : 08:35:06

Try this too

where
(Initiated = @Initiated and ((@method = 0 and Received = @Received)) or @method=1)

Madhivanan

Failing to plan is Planning to fail

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-08 : 05:58:17

Ok well, that sorta did the trick, I added another param today and now the numbers are off

so now I have.
@platform
@initiated bit
@received bit
@method bit
@awt bit

select
*
from table
where
platform = @platform
and awt = case when datalength(@awt) > 0 then @awt else awt end
and
(@method = 0 and (Initiated = @Initiated and Received = @Received))
or
(@method = 1 and (Initiated = @Initiated or Received = @Received))

when i run that, with @awt = null, thats fine i get 22 records and the data is perfect, when i put @awt = 1 then i get 1005 records, and i can see awt with 0's in the columns when i specified for it to only show with 1's

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 06:06:50

select
*
from table
where
platform = @platform
and awt = case when datalength(@awt) > 0 then @awt else awt end
and
((@method = 0 and (Initiated = @Initiated and Received = @Received))
or
(@method = 1 and (Initiated = @Initiated or Received = @Received)))

Madhivanan

Failing to plan is Planning to fail

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2009-09-08 : 06:34:49

Wow, I feel stupid, I even stared at your code for a bit wondering what you did that was different, I finally saw the extra () around the method...

Thank a bunch, my number are now correct!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 08:45:21

quote:
Originally posted by HalaszJ

Wow, I feel stupid, I even stared at your code for a bit wondering what you did that was different, I finally saw the extra () around the method...

Thank a bunch, my number are now correct!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources