sql查询参数为null不为null [英] sql query where parameters null not null

问题描述

我正在尝试执行 sql 查询并根据参数是否为空来动态构建 where 条件.我有这样的事情:

I am trying to do a sql query and to build the where condition dynamically depending if the parameters are null or no. I have something like this:

SELECT tblOrder.ProdOrder, tblOrder.Customer FROM tblOrder 
CASE WHEN @OrderId IS NOT NULL
THEN 
WHERE tblOrder.OrderId = @OrderId
ELSE
END
CASE WHEN @OrderCustomer IS NOT NULL
THEN
AND tblOrder.OrderCustomer = @OrderCustomer
ELSE
END
END

这不起作用，但这只是一个如何组装查询的小原型，因此如果 orderid 不为 null 则包含在 where 子句中，或者如果 ordercustomer 不为 null 则包含在 where 子句中.但是我在这里看到了问题，例如如果 ordercustomer 不为 null 但 orderid 为 null，则会出现错误，因为未包含 where 关键字.任何建议我如何解决这个问题.在此先感谢 Laziale

This doesn't work, but this is just a small prototype how to assemble the query, so if the orderid is not null include in the where clause, or if the ordercustomer is not null include in the where clause. But I see problem here, for example if the ordercustomer is not null but the orderid is null, there will be error because the where keyword is not included. Any advice how I can tackle this problem. Thanks in advance, Laziale

推荐答案

这应该做你想做的:

SELECT tblOrder.ProdOrder, tblOrder.Customer 
FROM tblOrder 
WHERE ( @OrderId IS NULL OR  tblOrder.OrderId = @OrderId )
AND   ( @OrderCustomer IS NULL OR  tblOrder.OrderCustomer = @OrderCustomer )
OPRION (RECOMPILE)

但正如评论所说，您应该包含 OPTION RECOMPILE 提示，否则性能会很差.

But as commented you should include the OPTION RECOMPILE hint, otherwise it will have bad performance.

值得一读:

• http://www.sommarskog.se/dyn-search-2008.html

这篇关于sql查询参数为null不为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！