SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

tech_1
Posting Yak Master

126 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.

thoughts?

James K
Flowing Fount of Yak Knowledge

3744 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
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
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000