Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Dynamic LIKE in WHERE
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

romanspanner
Starting Member

2 Posts

Posted - 07/14/2003 :  12:16:44  Show Profile  Reply with Quote
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

USA
298 Posts

Posted - 07/14/2003 :  12:37:20  Show Profile  Reply with Quote
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
Go to Top of Page

romanspanner
Starting Member

2 Posts

Posted - 07/14/2003 :  12:53:30  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000