sql查询其中参数为空而不是空 [英] 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
推荐答案
这应该可以满足您的需求:
This should do what you want:
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查询其中参数为空而不是空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!