MSSQL WHERE with IF

Question

我正在尝试获取执行以下操作的查询：

SELECT * FROM Test
WHERE
IF Condition = True
    Column1 = 'This'
    Column2 = 'That'
ELSE
    Column3 = 'This'
    column4 = 'That'

我唯一能找到的 where 子句中的 If 条件是 CASE。我无法找到允许多种条件的情况的示例。

到目前为止，我必须通过在 SELECT 语句之外使用 If 并复制该语句但更改 WHERE 子句来实现这一点。

Answer 1

SELECT * FROM Test
WHERE
(Condition = True
    AND Column1 = 'This'
    AND Column2 = 'That')
OR
(Condition = FALSE
    AND Column3 = 'This'
    AND column4 = 'That')

Answer 2

您可以使用 PROCEDURE 或 FUNCTION 来执行此操作。

例如，使用 PROCEDURE：

CREATE PROCEDURE test (IN _condition BOOLEAN, IN _this VARCHAR(255), IN _that VARCHAR(255))
BEGIN
    IF _condition THEN
        SELECT * FROM Test WHERE Column1 = _this AND Column2 = _that;
    ELSE
        SELECT * FROM Test WHERE Column3 = _this AND Column4 = _that;
    END IF;
END;

要运行它，只需执行CALL test(TRUE, 'This', 'That')

您可以将_condition替换为您自己的条件，例如：

CREATE PROCEDURE test1 (IN _number INT(11), IN _this VARCHAR(255), IN _that VARCHAR(255))
BEGIN
    IF _number >= 2016 THEN
        SELECT * FROM Test WHERE Column1 = _this AND Column2 = _that;
    ELSE
        SELECT * FROM Test WHERE Column3 = _this AND Column4 = _that;
    END IF;
END;

并执行CALL test1(2015, 'This', 'That')