使用Where子句中的Case构建动态查询

I have a stored procedure and I want implement the following query using Case Statement , but I am not sure how to do it . pseudocode of what I want is provided here :

我有一个存储过程，我想使用Case语句实现以下查询，但我不知道如何做到这一点。这里提供了我想要的伪代码：

declare @PI_X decimal(18);
declare @PI_y decimal (18);

SELECT F1, F2,F3
FROM TABLE T 
WHERE 
 CASE 
 WHEN @PI_X IS NULL THEN @PI_Y = T.Y
 WHEN @PI_Y IS NULL THEN @PI_X = T.X

It seems that using case statement for conditions is not true and its for values.

似乎对条件使用case语句不是真的，它的值也是如此。

NOTE:

注意：

I want to run this query in DB2 and SQL server , but really Data Base vendor is not important for me ,using sql dynamic query and (OR) in where clause has a performance hit . and I don't want it. I really like to know how it's possible to achieve such a logic using case in where clause.

我想在DB2和SQL服务器中运行此查询，但实际上数据库供应商对我来说并不重要，使用sql动态查询和（或）where子句有性能损失。我不想要它。我真的很想知道如何使用where子句中的case来实现这样的逻辑。

Could you please help me to handle this problem . Any help and suggestions would be so appreciated .

你能帮我解决这个问题吗？任何帮助和建议将非常感激。

4 个解决方案

#1

I'm not sure why your question is tagged with both SQL Server AND DB2... But I'll assume SQL Server...

我不确定为什么你的问题用SQL Server和DB2标记......但我会假设SQL Server ......

declare @PI_X decimal(18);
declare @PI_y decimal (18);

SELECT 
    T.F1, 
    T.F2,
    T.F3
FROM 
    TABLE T 
WHERE 
    (@PI_X = T.X OR @PI_X IS NULL)
    AND ((@PI_y = T.Y OR @PI_y IS NULL)
OPTION (RECOMPILE); -- Prevent's the forced scan operation cause by the use of "optional" parameters.

#2

The result of a CASE expression is a value, not an expression. You can't use a CASE expression to decide which code will run. You can only use it to choose what value will be used with your code.

CASE表达式的结果是值，而不是表达式。您不能使用CASE表达式来决定将运行哪些代码。您只能使用它来选择代码使用的值。

In this case, you can accomplish your goal like this:

在这种情况下，您可以像这样完成目标：

declare @PI_X decimal(18);
declare @PI_y decimal (18);

SELECT F1, F2,F3
FROM TABLE T 
WHERE 1 = 
 CASE 
 WHEN @PI_X IS NULL AND @PI_Y = T.Y THEN 1
 WHEN @PI_X IS NOT NULL AND @PI_Y IS NULL AND @PI_X = T.X THEN 1 
 ELSE 0 END

You could also try it like this:

你也可以这样试试：

declare @PI_X decimal(18);
declare @PI_y decimal (18);

SELECT F1, F2,F3
FROM TABLE T 
WHERE coalesce(@PI_X, T.X) = T.X AND coalesce(@PI_y, T.Y) = T.Y

Though this second option might produce unexpected results if @PI_y might be something other than NULL when @PI_x has a value. If you can guarantee one or the other of the two variables will have a value, but never both, then you could also simplify the first option to remove the extra @PI_X IS NOT NULL AND part of the expression.

虽然当@PI_x具有值时，如果@PI_y可能不是NULL，则第二个选项可能会产生意外结果。如果您可以保证两个变量中的一个或另一个将具有值，但从不两者都有，那么您还可以简化第一个选项以删除额外的@ PI_X IS NOT NULL和表达式的一部分。

#3

You can try out putting the if condition which will be readable however increasing the lines of code.

您可以尝试将if条件设置为可读，但增加代码行。

if(@PI_X IS NULL)
begin
select 
....
where 
T.Y=@PI_Y
end

else
begin
select 
....
where 
T.X = @PI_X
end

#4

Instead of using an OR in the WHERE clause you could also build 2 separate queries and use IF...ELSE... to decide which one to use; something along the lines of what Coder1991 suggested. Or you could use a UNION ALL construction to avoid the IF and eliminate any branching.

您也可以构建2个单独的查询，并使用IF ... ELSE ...来决定使用哪一个;而不是在WHERE子句中使用OR;符合Coder1991建议的内容。或者您可以使用UNION ALL构造来避免IF并消除任何分支。

SELECT F1, F2,F3
 FROM TABLE T 
WHERE @PI_X IS NULL 
  AND @PI_Y = T.Y

UNION ALL

SELECT F1, F2,F3
  FROM TABLE T 
 WHERE @PI_Y IS NULL 
   AND @PI_X = T.X

#1