Help with query

[Diego F.]

Hi, I'm trying to make a query to search a pattern in a SQL Server database.
I need something like this:

SELECT * FROM Table WHERE title LIKE @value ORDER BY title.

Then, I create a parameter for @value, and when I set its value I use: "%" +
value + "%".

The result of the query is the same as if I was using = operator. Am I
missing something?

Regards,

Diego F.

 

[Diego F.]

I solved that doing the following: I don't use the SqlParameters. Instead, I
modify the query.

string query = "SELECT * FROM Table WHERE " + title + " LIKE '%" + value +
"%' ";

Before I was trying to do parameters with the @.

When is it necessary using the SqlParameters?

 

[Bob Grommes]

Diego,

It's never *required* to use parameters, but it is almost always desireable
from a performance standpoint to use them whenever you can. The reason is
that the command text will not change every time it gets sent to SQL Server
and thus it will probably be cached and compiled only once, no matter how
many times you call it or what parameters you send along with it.

I haven't actually tried this but I suspect the following would work with
parameters (I'm making assumptions about Title's data type, but you get the
idea):

// assume the value to search by is in string strValue
cmd.CommandText = "SELECT * FROM Table WHERE Title LIKE @Title ORDER BY
Title";
cmd.Parameters.Add("@Title",SqlDbType.VarChar,strValue.Length).Value = "%" +
strValue + "%";

.... etc.

Also, keep in mind that a LIKE '%WHATEVER%' search will be slow as it cannot
make use of any indexes. LIKE 'WHATEVER%' is able to use indexes and will
perform much better. Therefore if you can get by with matching on the start
of the field that will perform better. If you really want to allow a match
anywhere within the field you might get better performance with SQL that
ends up evaluating to something like this:

.... WHERE Title LIKE 'WHATEVER%' OR Title LIKE '%WHATEVER%'

This way if there is a match at the start of the field it will be found
quickly and the query optimizer should bypass even trying the more general
%WHATEVER% search. This approach would only help if a significant
percentage of possible matches will be at the start of the field -- a common
scenario in many applications, but maybe not in yours.

--Bob