在 SQL LIKE 子句中使用 SqlParameter 不起作用 [英] Use of SqlParameter in SQL LIKE clause not working

问题描述

我有以下代码:

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"(不是参数值)，要么在查询中嵌入一些额外的引号(第二个样本).

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 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！