在SQL LIKE子句的SqlParameter不工作

问题描述：

我有以下的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 as well:

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 this does not work as well. What is going wrong? Any suggestions?

答

你想要的是：

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

（或编辑参数值包含％位居第一）。

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

否则，你要么（第一样品）搜索的的文字的@SEARCH（不是ARG值），或者你正在嵌入一些额外的报价为查询（第二个示例）。

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).

在某些方面，可能更容易有TSQL只需使用 LIKE @SEARCH ，并在来电处理它：

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.

在SQL LIKE子句的SqlParameter不工作

相关推荐