Please start any new threads on our new site at 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
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Best way for like search in SPROC?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

129 Posts

Posted - 02/02/2013 :  13:51:59  Show Profile  Reply with Quote
So I am trying to upgrade an old system that uses string concatination (SQL Injection) to perform searches against the SQL DB.
The code is massive.

So now I am trying to convert this to a SPROC. Here is the thing: the way it is done at the front end is that if anyone enters a wildcard, it does a LIKE search. If not, then does a simple match search.

I am wondering if there is a way I can do the same rather than having to check to see if the parameter contains a wild card then construct a different path to do a search if not, then do another type of query but without the LIKE.


James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/02/2013 :  15:34:49  Show Profile  Reply with Quote
You coulduse LIKE clause with or without a wild card. For example,
-- this is perfectly valid and correct

It is good that you are revising the code to avoid SQL Injection and parameterizing the query. One thing to note though, is that when you do that, sometimes you can run into a problem that is often referred to as parameter sniffing. This happens when SQL Server generates an optimized execution plan based on one set of parameters, but that plan turns out to be completely inefficient for another set of parameters. As an example, consider the two queries below:
SELECT Customer FROM Tbl WHERE Customer LIKE '%Smith';
SELECT Customer FROM Tbl WHERE Customer LIKE 'Smith%'
The query plan for the first select would not be able to use any index that may be on Customer column. If the query plan generated for that select is reused for the second query, it would miss out on the opportunity to use the index even though the second query could make use of such an index. If you run into that problem, you may find this article interesting read:
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