Resolve: Use of SqlParameter in SQL LIKE clause not working

Question

I have the following code:

 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 (SqlCommand command = new SqlCommand(sql, Connection))
    {       
    command.Parameters.AddWithValue("@SEARCH",searchString);
    .
    .
    .
    }

This does not work, I tried this as well:

 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 (SqlCommand command = new SqlCommand(sql, Connection))
    {       
    command.Parameters.AddWithValue("@SEARCH","'%"+searchString + "%'");
    .
    .
    .
    }

but this does not work as well. What is going wrong? Any suggestions?

Answer 1

What you want is:

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

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

Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).

In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:

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

Either approach should work.

Answer 2

You could do "LIKE @SEARCH" and in your C# code, do searchString = "%" + searchString + "%"

Answer 3

Thanx a ton!!!!!!!!!!!!!