SQL 条件式 Where [英] 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 条件式 Where的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！