CASE WHEN在WHERE条件逻辑中

huangapple go评论63阅读模式
英文:

CASE WHEN in WHERE condition logic

问题

我有以下查询:

SELECT name, address, phone
FROM users
WHERE
    CASE @ViewType
    WHEN 'AR' THEN phone IS NULL
    WHEN 'PO' THEN phone IS NOT NULL
    ELSE 1=1 END

ViewType 是参数,但我在我的 SQL 中收到了错误。

英文:

I have query like below

SELECT name, address, phone
FROM users
WHERE
    CASE @ViewType
    WHEN 'AR' THEN phone IS NULL
    WHEN 'PO' THEN phone IS NOT NULL
    ELSE 1=1 END

ViewType is parameter, but I received an error in my SQL.

答案1

得分: 2

你想要布尔逻辑:

从用户中选择姓名、地址、电话
来自
    (@viewtype = 'AR'且电话为空)
 或(@viewtype = 'PO'且电话不为空)
 或@viewtype不在('AR','PO')中

如果'AR''PR'是参数的唯一可能值,那么最后的谓词可以被移除。

为了提高参数化查询的性能,考虑使用 option(recompile),这样可以为参数的当前值生成新的计划。

英文:

You want boolean logic:

select name, address, phone 
from users 
where
    (@viewtype = 'AR' and phone is null)
 or (@viewtype = 'PO' and phone is not null)
 or @viewtype not in ('AR', 'PO')

If 'AR' and 'PR' are the only possible values of the parameter, then the last predicate can be removed.

For performance with such parameterized query, consider using option(recompile), so that a new plan is generated for the current value value of the parameter.

huangapple
  • 本文由 发表于 2023年5月25日 15:40:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76329932.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定