Friday, February 17, 2012

Doing away with like '%%'

Here's the setup

@.searchcondition varchar(50) --is a parameter passed into a stored procedure

IF(@.searchconditions is null)

BEGIN

SET @.searchcondition = '%'

END

ELSE

BEGIN

SET @.searchcondtion = '%' + @.searchcondition + '%'

END

select * from sometable

where somecolumn like @.searchcondition

Now the procedure is fine if the user passes a string, but if it's null, it seems really inefficient to search for '%'. Is there a generalized approach, other than say putting slightly different versions of the query in an if block?

Have you considering using the Fulltext features in SQL Server 2005 rather than LIKE?|||I'm trying to make minor optimizations to a setup I do not have full control of. Thanks for the pointer to the fulltext stuff though, I'll definitely be using that on my future projects.|||

Did you already try the following:

select * from sometable

where (somecolumn like @.searchcondition) or (@.searchcondition is null)

?

|||Nice, works like a charm. Thanks for the tip.

No comments:

Post a Comment