SQL Case 语句在 where 子句中指定条件？

Question

我有以下查询:

    SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND
@ActiveInactive =
CASE 
WHEN 'Active' THEN (o.[orderactivedate] > o.[orderinactivedate])
WHEN 'Inactive' THEN (o.[orderactivedate] < o.[orderinactivedate]) 
END

这返回

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

我将如何让它发挥作用？说如果参数是“事件”然后返回具有以下条件的记录？

Answer 1

你可以用另一种方式做到这一点:

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])
OR   (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))