Using SqlParameter in SQL LIKE not working

Question

I have the following code:

const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');"; using (var command = new SqlCommand(Sql, Connection)) { command.Parameters.AddWithValue("@SEARCH", searchString); ... } 

This does not work, I tried this too:

 const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );"; using (var command = new SqlCommand(Sql, Connection)) { command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'"); ... } 

but it does not work. What is going wrong? Any suggestions?

Answer 1

What would you like:

 tblCustomerInfo.Info LIKE '%' + @SEARCH + '%' 

(or edit the parameter value to include% first).

Otherwise, you either (the first sample), look for the letter "@SEARCH" (not the arg value), or insert some additional quotation marks in the query (second sample).

In a sense, it might be easier to have TSQL just use LIKE @SEARCH and process it at the caller:

 command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%"); 

Any approach should work.

Answer 2

Instead of using:

 const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');"; 

Use this code:

 const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%' + @SEARCH + '%' OR tblCustomerInfo.Info LIKE '%' + @SEARCH + '%');"; 

Answer 3

Just a little more careful with the slight difference between the Add and AddWithValue methods . I had a problem below when I used the Add method and set the wrong SqlType parameter.

• nchar and nvarchar can store Unicode characters .
• char and varchar cannot store Unicode characters .

For example:

 string query = " ... WHERE stLogin LIKE @LOGIN "; SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.Char, 255) { Value = "%" + login + "%" }; command.Parameters.AddWithValue(p.ParameterName, p.Value); //works fine!!! command.Parameters.Add(p); // won't work 

When I changed SqlType to NVarChar , both methods worked fine for me.

 SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.NVarChar, 255) { Value = "%" + login + "%" }; command.Parameters.AddWithValue(p.ParameterName, p.Value); //worked fine!!! command.Parameters.Add(p); //worked fine!!! 

Answer 4

You can do LIKE @SEARCH and in your C # code do

 searchString = "%" + searchString + "%"