基於參數值的帶有“AND”的 case 語句的儲存過程

我的數據庫中有一個表，其中儲存了成功和失敗的登錄嘗試。我正在創建一個儲存過程，允許我們刪除早於 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 字元串與變數連接起來。不知道我在這裡做錯了什麼。不過，我對動態查詢相當陌生。

我認為您在這裡甚至不需要動態 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.

引用自：https://dba.stackexchange.com/questions/205161