how to get LIKE clause to work in ADO.NET and SQL Server

Question

I am doing a really simple query in ASP.NET, but after I inserted the LIKE clause it stops working.

Example:

String sql = " SELECT * 
                 FROM Products 
                WHERE ID = @MYID 
                  AND Name LIKE '%@MYNAME%' ";
SqlCommand command = new SqlCommand(sql, cn);


command.Parameters.AddWithValue("@MYID", MYID.Text);

command.Parameters.AddWithValue("@MYNAME", MYNAME.Text);

If I removed the LIKE it works. Hence I am thinking its to do with the '' quotes?

Answer 1

The original code is confusing the text of the SQL statement with the content of the parameter. Your code should actually look like this:

string sql = "SELECT * 
              FROM Products 
              WHERE ID = @MyID
              AND Name LIKE @MyName";
using (SqlCommand command = new SqlCommand(sql, cn))
{
    command.Parameters.AddWithValue("@MyID", MyID.Text);
    command.Parameters.AddWithValue("@MyName", "%" + MyName.Text + "%");
    // Etc.
}

The % signs need to be part of the parameter value, and you don't need the single quotes at all when using binding parameters.

Answer 2

Just a note to say that using LIKE with an initial wildcard is almost always a very bad idea, because the query won't use any indexes on that column. In this case you can probably get away with because it looks like the filter on the ID column will limit you to one record, but generally what you need to do instead is put a full-text index on the name column and write the query like this:

... WHERE CONTAINS(name, @MyName)