主页 / dba / 问题 / 205161
Accepted
pmdci
Asked: 2018-04-28 03:55:21 +0800 CST

基于参数值的带有“AND”的 case 语句的存储过程

  • 772

我的数据库中有一个表，其中存储了成功和失败的登录尝试。我正在创建一个存储过程，允许我们删除早于 X 天的记录。到目前为止一切都很好，但我想提高一个档次（或两个档次），并允许我们指定是否删除有关 [Success] 列是真、假还是两者的记录。不过，我在连接需要执行的脚本时遇到了一些问题。

这是我到目前为止所做的：

-- CREATE PROCEDURE [dbo].[sp_delete_log_attempts]
DECLARE @backDays INT = 1 -- Default to 30 days (one test finishes)
DECLARE @successArg BIT = NULL -- default to both true and false success logins
DECLARE @successAnd VARCHAR(50)
DECLARE @query VARCHAR(MAX)

SET @successAnd = CASE
WHEN @successArg = 'true' THEN
    'AND [Success] = ''true'''
WHEN @successArg = 'false' THEN
    'AND [Success] = ''false'''
ELSE
    'AND [Success] = ''true'' OR [Success] = ''false'''
END

PRINT @successAnd -- just for debugging purposes

SET @query = 'SELECT * FROM [audit].LoginAttempt WHERE [TimeStamp] <= DATEADD(day,-' + @backDays + ', GETDATE())'
EXEC @query

此时我只是尝试根据@backDays 变量选择行，但我无法将@query 字符串与变量连接起来。不知道我在这里做错了什么。不过，我对动态查询相当陌生。

stored-procedures t-sql

1 个回答

  1. Best Answer

    我认为您在这里甚至不需要动态 SQL。您可以在查询中使用变量。

    SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND CASE
                   WHEN @successArg IS NOT NULL
                     THEN (SELECT 1
                                  WHERE [Success] = @successArg)
                   ELSE 1
                 END = 1;

    （未测试，因为没有提供样本数据。只是为了演示这个想法。）

    如果你仔细看，你会发现，整个CASE ... END是一个=操作的左操作数。右边的操作是1。

    不幸的是，SQL Server 不知道我们可以直接从CASE ... END. （其他的，例如 PostgreSQL。）所以我们必须使用它可以用于布尔运算的表达式来欺骗它。这就是我们使用=操作的原因。

    现在我们需要一种在满足我们实际想要测试的条件时使该操作评估为真的方法。所以这个想法是，如果我们决定满足它们，我们将返回一个指定的值作为=. 另一方面，我们只是按字面意思使用该值。那=将是真的。如果我们的条件不满足，我们会为左侧返回任何其他值，并且=不会为真。作为表示满足条件的值，让我们选择1. 它接近于我们可能想到的布尔值的表示。（但我们几乎可以选择任何东西（NOT NULL，否则我们必须将=操作更改为IS NULL）。）。

    1那么，当条件满足时，我们怎样才能让我们的左表达式返回呢？

    好吧，我们可以使用CASE ... END语句根据某些条件返回一个值。你已经知道CASE ... END. 它有点类似于类 C 语言中的 a switchorif else结构（或在其他过程语言中具有不同的名称）。

    我们需要测试的是输入变量@successArg是否为空。如果它为空，则意味着调用者不在乎记录的登录尝试是否成功。否则， 的值@successArg表示他只想要成功的登录 ( @successArg = 1) 还是只想要不成功的 ( @successArg = 0)。这将为我们提供最重要的案例：要么忽略成功，要么将其考虑在内。因此，我们的分支中有两个（主要）CASE ... END分支@successArg IS NOT NULL；

    让我们从更简单的情况开始，当@successArg IS NULL. 那是'ELSE'分支。在这里，我们不关心登录尝试是否成功。对于任何行，成功或不成功（认为它是一个布尔表达式）总是正确的。所以我们只返回我们的指标，这是一个匹配，1。

    当@successArg IS NULL,@successArg保存值时，行必须在列中[Success]。仅当 时才给出@successArg = [Success]。如前所述，@successArg = [Success]在那种情况下，SQL Server 无法单独处理。因此，我们需要在当前行1返回真实指示值@successArg = [Success]，否则返回其他值。

    这样做的一种方法是另一种内部查询，CASE ... WHEN或者我们可以使用相关子查询。它是“相关的”，因为它使用外部查询当前行的列值。它是“sub”，因为它是“sub”，从外部查询的位置来看。此外，我们使用 SQL Server 中的特性，如果该查询为真（或不存在），则 a SELECTwith noFROM将产生一条记录WHERE，否则为空集。因此，让我们生成一个记录，其中一列包含1when @successArg = [Success]。这样的记录，只有一列，如果需要，将隐式转换为标量，因此它适合我们=在外面的操作。当 时@successArg <> [Success]，该子查询将产生一个空集。NULL. 事实NULL = 1并非如此，这将为我们做到这一点。

    关于这一点的一个旁注：它也可以在没有CASE ... END布尔表达式的情况下完成：

    SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND (@successArg IS NULL
                   OR [Success] = @successArg);

    这遵循了我们从上面的想法。如果@successArg IS NULL我们想要“真实”而不考虑其他任何事情。否则我们只想要“真”时[Success] = @successArg。在任何其他情况下“假”。（注意：需要括号来覆盖ANDover的优先级，OR因为前面的条件。）

    就查询的执行方式以及性能而言，我认为这不会产生重大影响（如果有的话）（但坦率地说，我不确定这一点）。不过，解决方案CASE ... WHEN可能更容易阅读，因此更容易维护。另一方面，仅布尔解决方案也适用于不具备CASE ... END.

    • 6

相关问题