问题描述
我正在尝试执行 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.
值得一读:
这篇关于sql查询参数为null不为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！