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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Best way for like search in SPROC?

Author  Topic 

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-02 : 13:51:59
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.

thoughts?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-02 : 15:34:49
You coulduse LIKE clause with or without a wild card. For example,
-- this is perfectly valid and correct
SELECT Customer FROM Tbl WHERE CUSTOMER LIKE 'Smith'


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: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Go to Top of Page
   

- Advertisement -