Is using a like the best way

Hi

I have the following stored proc where all the parameters are optional. is the following the best way to implement a search on a view .

declare @sourceID as varchar(500)
declare @targetID as varchar(500)
declare @deskID as varchar(500)
declare @messageType as varchar(500)

set @sourceID = '%'
set @targetID = '%'
set @deskID = '36'
set @messageType = '%'


select *
from viewGetSourceTargetRouteList
where cast(sourceID as varchar(500)) like @sourceID and
cast(targetID as varchar(500)) like @targetID and
cast(deskID as varchar(500)) like @deskID and
messageType like @messageType



thanks
DJ

Hi,
Why are you accepting all ID values in varchar format when it is stored in tables as numeric.
Also Like will hurt your query performance badly

Have a look at

http://www.sommarskog.se/dyn-search.html

Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

you don't need to CAST here .... Read about STR() function in BOL

check this...

Declare @Tab Table (ID int, Name varchar(10))
insert into @Tab select 101,'SexyDog'
insert into @Tab select 202,'SexyCat'
insert into @Tab select 301,'SexyRat'
insert into @Tab select 411,'SexyRat'
insert into @Tab select 10011,'SexyRat'

select *from @Tab where str(id) like '%1%'
select *from @Tab where str(id) like '%2%'
select *from @Tab where str(id) like '%00%'

Madhu