SQL Server Performance Forum – Threads Archive

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

---

]]>