searching criteria - 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

searching criteria

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 21:42:08

hi all... how do i write my where clause if i wanna search BETWEEN something to something, but at the same time, find ALL if user send nothing (''), NOT searching for '' column... and also find date if they send a date, and if they dont send date, do not consider date at all(find all at any dates).. is this possible to in one where clause without any IF statement... thanks..

WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom+ '%' AND
d.LocID BETWEEN @LocFrom AND @LocTo AND
d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom AND
d.RecvDate <= @Date

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:00:26

[code]
WHERE d.Ownership LIKE coalesce('%' + nullif(@ClientID, '') +'%', d.Ownership)
AND d.WhsID LIKE coalesce('%' + nullif(@WhsFrom, '') + '%', d.WhsID)
. . .
AND d.RecvDate <= coalesce(@Date, d.RecvDate)
[/code]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 22:33:32

so how about between?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:36:12

between is actually


WHERE cola >= value1
and   cola <= value2

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 22:36:40

i've tried out
d.LocID BETWEEN coalesce('%' + nullif(@LocFrom, '') + '%', d.LocID) AND coalesce('%' + nullif(@LocTo, '') + '%', d.LocID)

for BETWEEn, but it still search for ''

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 22:37:49

okay ill try that first

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 22:49:11

how come this still return me all the ' ' LocID column ?

d.LocID >= coalesce('%' + nullif(@LocFrom, '') + '%', d.LocID) AND
d.LocID <= coalesce('%' + nullif(@LocTo, '') + '%', d.LocID) AND

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 22:52:13

and d.RecvDate <= coalesce(@Date, d.RecvDate) return dates >@Date as if this statement is being ignored.. tq khtan, it always be u.. hahahahha

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:55:45

" how come this still return me all the ' ' LocID column ?"
You mean you have rows with LocID is empty string '' ?

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-05 : 23:16:16

yup, it only search for empty string if i input@locFrom='' and @locto='' for the BETWEEN statement.. whereas i expect it could search any locid if i didnt input any range..

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 23:42:07

[code]
declare @table table
(
ID int identity(1,1),
LocID varchar(10)
)

insert @table (LocID)
select 'L1' union all
select 'L2' union all
select 'L3' union all
select '' union all
select 'L4' union all
select 'L5'

declare @LocFrom varchar(10),
@LocTo varchar(10)

select @LocFrom = 'L2',
@LocTo = 'L4'

select *
from @table t
where t.LocID >= coalesce(nullif(@LocFrom, ''), t.LocID)
and t.LocID <= coalesce(nullif(@LocTo, ''), t.LocID)
/*
ID LocID
----------- ----------
2 L2
3 L3
5 L4
*/

select @LocFrom = '',
@LocTo = ''

select *
from @table t
where t.LocID >= coalesce(nullif(@LocFrom, ''), t.LocID)
and t.LocID <= coalesce(nullif(@LocTo, ''), t.LocID)

/*
ID LocID
----------- ----------
1 L1
2 L2
3 L3
4
5 L4
6 L5
*/
[/code]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-06 : 00:45:49

only god knows, why this one cannot work like urs..

WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom + '%' AND

d.LocID >= coalesce(nullif(@LocFrom, ''), d.LocID) and
d.LocID <= coalesce(nullif(@LocTo, ''), d.LocID) and

d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 00:49:09

well, you can post your table structure and some sample data

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 00:51:22

" only god knows "
We also have Goddess and Saint in this forum.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-06 : 00:55:48

this is suppose to work.. i paste my SP to query analyzer, evthing ok when i set

declare @LocFrom varchar(10),
@LocTo varchar(10)

select @LocFrom = '',
@LocTo = ''

but when i exec my SP like this
exec SP_InvRpt_ByLocID

@ClientID ='',
@WhsFrom ='',
@WhsTo ='',
@ZoneFrom ='',
@ZoneTo ='',
@ItemFrom ='',
@ItemTo ='',
@LocFrom ='',
@LocTo ='',
@CustomlotnoFrom ='',
@CustomlotnoTo ='',
@Date ='',
@Udf ='N'

it only find me the '' column...
dunno what's the problem.. must be my side, not yurs... :(

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-06 : 00:58:19

oohhh khtan!!!!!!!!!!!

i hope u dont get mad of me... i use different db.. and execute my SP in different db..
soo sorry to waste your time... arghhhh.. and my sorry for myself wasting my own time... ehheheheh...

thankssssss a LOT

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 01:08:19

quote:
Originally posted by maya_zakry

oohhh khtan!!!!!!!!!!!

i hope u dont get mad of me... i use different db.. and execute my SP in different db..
soo sorry to waste your time... arghhhh.. and my sorry for myself wasting my own time... ehheheheh...

thankssssss a LOT

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-06 : 02:08:29

khtan,

want to check out something... is this ok with datetime ? to compare date between using method u said b4? coz ive kinda get convert varchar datetime error in my crystal report when i update the SP..

d.RecvDate >= coalesce(nullif(@DateFrom, ''), d.RecvDate) and
d.RecvDate<= coalesce(nullif(@DateTo, ''), d.RecvDate)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 02:14:04

We need to se the compete query!
Most often when looking for people who have sent NOTHING (as stated in OP), there must be a LEFT JOIN or a subquery looking for no records sent.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 02:28:37

quote:
Originally posted by maya_zakry

khtan,

want to check out something... is this ok with datetime ? to compare date between using method u said b4? coz ive kinda get convert varchar datetime error in my crystal report when i update the SP..

d.RecvDate >= coalesce(nullif(@DateFrom, ''), d.RecvDate) and
d.RecvDate<= coalesce(nullif(@DateTo, ''), d.RecvDate)

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

The convert error is due to converting of '' to datetime. It can't convert empty string '' to datetime.

@DateFrom is datatime datatype right ? What do you pass to the parameter if user did not specify the DateFrom and DateTo ?
You should pass in NULL value if the user did not specify any date range.

Then change to


d.RecvDate >= coalesce(@DateFrom, d.RecvDate) and
d.RecvDate<= coalesce(@DateTo,    d.RecvDate)

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-07 : 03:09:05

hi khtan,
but than if i change that, it search nothing if i send @DateFrom='' and @DateTo='' to the SP, whereas i want it to search all dates if user input nothing for datefrom and dateto..

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources