SQL条件条件 [英] SQL Conditional Where

问题描述

我有一个名为spGetOrders的存储过程，该存储过程接受一些参数：@startdate和@enddate。这将查询订单表。表中的一列称为 ClosedDate。如果尚未关闭订单，则此列将为NULL；如果尚未关闭日期，则此列将为。我想添加一个@Closed参数，该参数需要一个值。在一个简单的世界中，我能够做到。

I have a Stored Procedure called spGetOrders which accepts a few parameters: @startdate and @enddate. This queries an "Orders" table. One of the columns in the table is called "ClosedDate". This column will hold NULL if an order hasn't been closed or a date value if it has. I'd like to add a @Closed parameter which will take a bit value. In a simple world, I'd be able to do..

select * from orders o
where o.orderdate between @startdate AND @enddate
and (if @Closed = 1 then o.ClosedDate IS NULL else o.ClosedDate IS NOT NULL)

很显然，这是行不通的。.我也正在寻找动态sql，这是我的最后选择，但是开始看起来就像答案。.

Obviously, that's not going to work.. I'm also looking at dynamic sql which is my last resort, but starting to look like the answer..

请帮助。.

推荐答案

尝试一下：

select * from orders o
where o.orderdate between @startdate AND @enddate
and ((@Closed = 1 And o.ClosedDate IS NULL) Or (@Closed = 0 And o.ClosedDate IS NOT NULL))

在混合时要小心where子句中的AND和OR。在执行此操作时，控制评估顺序的括号非常重要。

Be vary careful about mixing AND's and OR's in the where clause. When doing this, the parenthesis to control the order of evaluation is VERY important.

这篇关于SQL条件条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！