Like clause and prepared statement

Question

I am trying to make an SQL request with a LIKE clause, using prepared statement.

Here is the code :

using (SqlConnection Connection = new SqlConnection(ConnectionString))
      {
         Connection.Open();
         string Query = "SELECT TOP 10 Field FROM Table WHERE Field LIKE '@pseudopart%'";
                using (SqlCommand Command = new SqlCommand(Query, Connection))
                {
                    Command.Parameters.AddWithValue("@pseudopart", pseudoPart);
                    using (SqlDataReader Reader = Command.ExecuteReader())
                    {
                        if (!Reader.HasRows)
                            return PossibleMatch;
                        while (Reader.Read())
                        {
                            PossibleMatch.Add(Reader["Field"].ToString());
                        }
                    }
                }
            }

Reader is always empty, what am i doing wrong ?

The answer here: http://stackoverflow.com/questions/303149/parameterized-queries-with-like-and-in-conditions. The question is a duplicate. — Dmitry Egorov, May 29 '15 at 14:00
@DmitryEgorov Agreed. Correct answer is at the linked question. — RBarryYoung, May 29 '15 at 14:02
as Soner Gönül's answer below shows when you are using parameters like this it will automatically try and add the single quotes for you. So when you manually enter them it doesnt workout like you would think. — CathalMF, May 29 '15 at 14:02
@CathalMF Just a nit, (but an important one for those trying to understand this), when you are using parameters, it does not "add the quotes for you", nor anything like that (because then it would be vulnerable to SQL Injection). Rather it passes and uses it *as a parameter* in SQL, which works just like parameters and variables in most other languages: no quotes are needed once the parameter/variable has been assigned. — RBarryYoung, May 29 '15 at 14:09

Soner Gönül · Accepted Answer · 2015-05-29T14:11:38.257

Since you using single quote, it sees your @pseudopart% part as a string literal, not a parameter.

That's why you are filtering your Field column with @pseudopart% string, not the value of pseudoPart variable. That's why your reader is empty.

Use it instead like;

string Query = "SELECT TOP 10 Field FROM Table WHERE Field LIKE @pseudopart";
..
Command.Parameters.AddWithValue("@pseudopart", pseudoPart + "%");

By the way, don't use AddWithValue method. It may generate unexpected results sometimes. Use Add() method overloads to specify your parameter SqlDbType and it's size.

And I must say, TABLE is a reserved keyword in T-SQL. You should use it with square brackets like [TABLE]. Some database managers don't consider the keyword cases (Table - TABLE) but SQL Server does consider it by default as far as I know.

Best option is change it to non-reserved word.

score 0 · Answer 2 · answered May 29 '15 at 13:59

You should add the % in your parameter and the ado.net provider will solve it for you in your sql query, for sample:

 string Query = "SELECT TOP 10 Field FROM Table WHERE Field LIKE @pseudopart";
 using (SqlCommand Command = new SqlCommand(Query, Connection))
 {
     Command.Parameters.AddWithValue("@pseudopart", string.Concat(pseudoPart, "%"));

Like clause and prepared statement

2 Answers2