where clause - 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

General SQL Server Forums

New to SQL Server Programming

where clause

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-08 : 11:24:51

hi all...!

i wonder how to ignore the where clause(i mean partially) when the parameter is null? i mean :-
if parameter @tempudf1 is set to null, then i need to search any values (including null) from the recvudf1 column.. but if @tempudf1='something', i need to search only values that contains 'something' from column recvudf1..

if i do like this :-
where d.recvudf1 like isnull(@tempudf1,'%') it will exclude the <NULL> values in the column recvudf1 whereas i need to search all no matter it null or not... thanks..

note : i could have 1-10 recvudf so the where clause will look something like this :-
where d.recvudf1 like isnull(@tempudf1,'%') AND
d.recvudf2 like isnull(@tempudf2,'%') AND
d.recvudf3 like isnull(@tempudf3,'%') --> to recvudf10

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 11:27:42

[code]Where (@tempudf1 is NULL or d.recvudf1 = @tempudf1)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-08 : 11:47:51

i dont think so, coz i think this will return values with 'something' and <NULL>.. as i stated b4 i need to find only d.recvudf1='something' if the param is 'something'... and return everything if the param is NULL

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 13:30:21

"i think this will return values with 'something' and <NULL>.."
No. It won't. It will do exactly what you want.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-08 : 19:33:05

i dunno, ive tried it out.. it failed to search for @tempudf1='9K1009419895001008' in column recvudf1, but if the @tempudf1=NULL, it could find what i expect to find.. tq btw

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-08 : 19:41:44

oh harsh_!!
yeay somehow it works for one statement.. sorry my mistake.. but, could u correct my understanding on this.. coz i thought when there's OR operator, it returns any true statement..
Where (@tempudf1 is NULL or d.recvudf1 = @tempudf1)--> that's why i tot it will return both

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 22:06:32

Logic is really very simple here. @tempudf1 variable can either be NULL or it will contain some value. So both the conditions in OR can not be true at any single point of time (In fact both conditions are mutually exclusive). That's why it works !!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-08 : 22:31:03

yea.. :P
it seems that i confuse wif (d.recvudf1 is null or d.recvudf1=@tempudf1)
that's y luv this forum so much... yeehooo...

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-09 : 01:48:49

hello again..

one parameter is fine.. how do i search for the next parameter?? how do i search
from this table columns:-
row1 - d.recvudf1='rt54' d.recvuf2='rt55'
row2 - d.recvudf1='we3' d.recvudf2=NULL
row3 - d.recvudf1='rt54' d.recvudf=NULL

when being given @tempudf1='rt54' & @tempudf2='rt55' ---> suppose to get:-
d.recvudf1='rt54' d.recvuf2='rt55'

and when being given @tempudf1='rt54' & @tempudf2=NULL ---> suppose to get:-
d.recvudf1='rt54' d.recvuf2='rt55'
d.recvudf1='rt54' d.recvudf=NULL

thank u so much

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-09 : 01:57:21

So what's the big deal? Join two conditions with AND:

Where (@tempudf1 is NULL or d.recvudf1 = @tempudf1) and
      (@tempudf2 is NULL or d.recvudf2 = @tempudf2)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 01:58:28

[code]
declare @table table
(
recvudf1 varchar(10),
recvudf2 varchar(10)
)
insert into @table
select 'rt54', 'rt55' union all
select 'we3', NULL union all
select 'rt54', NULL

declare @tempudf1 varchar(10),
@tempudf2 varchar(10)

select @tempudf1 = 'rt54', @tempudf2 = 'rt55'

select *
from @table
where (
@tempudf1 is null
or recvudf1 = @tempudf1
)
and (
@tempudf2 is null
or recvudf2 = @tempudf2
)
/*
recvudf1 recvudf2
---------- ----------
rt54 rt55
*/

select @tempudf1 = 'rt54', @tempudf2 = NULL

select *
from @table
where (
@tempudf1 is null
or recvudf1 = @tempudf1
)
and (
@tempudf2 is null
or recvudf2 = @tempudf2
)

/*
recvudf1 recvudf2
---------- ----------
rt54 rt55
rt54 NULL
*/
[/code]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-09 : 04:18:00

yup yup you're rite.. khtan!! thanks for making my head clear.. ><

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources