SQL查询中的条件Where子句

Question

我正在开发一个用于生成报告的MVC应用程序。我提供了许多搜索选项，例如下面。

Customer id
Customer name
Customer E-mail
State
Country 

用户1：

如果某些用户只会输入一些值，例如

Customer id = 1
Customer name = A

默认情况下，其他参数将作为 null 值传递给存储过程。

Customer E-mail
State
Country 

用户2：

如果有些用户只会输入一些值，比如：

客户电子邮件=[e-mail]

那么其他参数将默认传递为 null 值到存储过程中。

Customer id
Customer name
State
Country 

如何在存储过程中使用SQL查询中的where子句？我们可以像下面这样做吗？

string qry = select * from table_name where status != d

if (@customerID!=null)
    qry = qry + "and customer_id=@customerID"
if (@customerName!=null)
    qry = qry + "and customer_name=@customerName"

请告诉我最佳的解决方案。

谢谢， Velu

- Velu

Answer 1

如果你正在创建动态SQL，则可以像上面那样操作：

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =  'SELECT * FROM TABLE '
if (@customerID IS NOT NULL)
    SQL = SQL + " AND customer_id = @customerID"

或者另一种选择是像处理其他例外情况一样处理它

SELECT *
FROM TABLE
WHERE (@customerID IS NULL OR customer_id = @customerID)

我更喜欢第二个，因为它使用了参数化变量。第一个例子需要更加注意恶意输入。

- Dustin Laine

---

我更倾向于第二种方法，因为它利用了参数化变量。虽然不是最优解，请参考http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/do-you-use-column-param-or-param-is-null。您可以使用`sp_executesql`来构建安全字符串。 - SQLMenace

我同意，但是在相当大的应用程序中，我也没有遇到过它的问题。因此，必须权衡安全/性能之间的平衡。总的来说，我确实同意这不是最佳执行计划。 - Dustin Laine

不是吹毛求疵，但这个 if (@customerID != null) 不会捕捉到 null 值，你需要写成 if (@customerID IS NOT null)。 - SQLMenace

Answer 2

您可以使用动态SQL，但更简单的方法是：

WHERE (ISNULL(@param1,1) = 1 OR [col1] = @param1)
    AND (ISNULL(@param2,1) = 1 OR [col2] = @param2)
    AND ...

- Christo

---

你刚才用那个不支持 SARGable 的查询语句把索引都废了。 - SQLMenace

@SQLMenace，@Remus：有趣的是你们如何预测出一个数据库模式和软件的性能问题，而你们没有看过这些东西，并且也没有运行过任何查询。任何人都知道这最好是通过专门设计的存储过程来处理性能问题，但我觉得这有点超出了问题的背景。OP在比较null，这已经设置了讨论的水平，你们认为呢？ - Christo

Answer 3

你需要将所有变量作为参数传递到存储过程中，然后在其中执行逻辑。

SqlCommand cmd  = new SqlCommand("STORED_PROC_NAME", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));
var rdr = cmd.ExecuteReader();

- Chris Kooken