Dynamic LIKE in WHERE - 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

Site Related Forums

Article Discussion

Dynamic LIKE in WHERE

Author

Topic

romanspanner
Starting Member

2 Posts

Posted - 2003-07-14 : 12:16:44

I am trying to implement dynamic LIKE filtering in one of my sp's according to something I saw in one of the articles on this site (typically, I can't actually find the original article anymore!).

The suggestion was to do the following;

create procedure sp_my_filter
(
@pmyfilter varchar(100)
)
as
select *
from tbl_mytable
where (myfield like '%' + @pmyfilter + '%' or @pmyfilter is null)

NB myfield allow NULLs

Problem is, it doesn't work...at all! It doesn't appear to apply any filter at all, i.e. all records are returned.

Am I doing something wrong?

Thanks in advance,

Roman

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-14 : 12:37:20

quote:

create procedure sp_my_filter
(
@pmyfilter varchar(100)
)
as
select *
from tbl_mytable
where (myfield like '%' + @pmyfilter + '%' or @pmyfilter is null)
NB myfield allow NULLs
Problem is, it doesn't work...at all! It doesn't appear to apply any filter at all, i.e. all records are returned.
Am I doing something wrong?

Yes

if(@pmyfilter IS NOT NULL)
Set @pmyfilter = '%' + @pmyfilter + '%'

select *
from tbl_mytable
where (myfield like Coalesce(@pmyfilter,myfield))

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

romanspanner
Starting Member

2 Posts

Posted - 2003-07-14 : 12:53:30

I thought I couldn't use COALESCE on NULLable fields?

Actually, I've fixed - I hadn't dimension'ed the parameter in my sp;

@pmyfilter varchar

instead of

@pmyfilter varchar(100)

BTW Both methods work, but if you do a trace and compare the two, the LIKE method takes half the time as the COALESCE method. Perhaps it's my table design, coz I thought coalesce was quicker.

Thanks for the response,

Romand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources