首頁猿問如何在T-SQL存儲過程中使用可選參數？

如何在T-SQL存儲過程中使用可選參數？

MySQL SQL Server

四季花海 2019-07-05 12:55:33

如何在T-SQL存儲過程中使用可選參數？我正在創建一個存儲過程來對表進行搜索。我有許多不同的搜索字段，所有這些都是可選的。是否有一種方法可以創建一個存儲過程來處理這個問題？假設我有一個包含四個字段的表：ID、FirstName、LastName和title。我可以這樣做：CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = nullAS BEGIN SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = ISNULL(@FirstName, FirstName) AND LastName = ISNULL(@LastName, LastName) AND Title = ISNULL(@Title, Title) END這類作品。但是，它忽略了FirstName、LastName或title為NULL的記錄。如果沒有在搜索參數中指定title，我希望包括標題為NULL的記錄-對于FirstName和LastName來說是相同的。我知道我可能可以使用動態SQL來完成這個任務，但我想避免這種情況。

查看完整描述

3 回答

慕少森

TA貢獻2019條經驗獲得超9個贊

基于給定參數的動態更改搜索是一個復雜的主題，通過另一種方式進行搜索，即使只有很小的差別，也會產生巨大的性能影響。關鍵是要使用索引，忽略緊湊的代碼，忽略對重復代碼的擔憂，必須制定一個良好的查詢執行計劃(使用索引)。

閱讀這篇文章，并考慮所有的方法。您的最佳方法將取決于您的參數、數據、模式和實際使用情況：

Erland Sommarskog在T-SQL中的動態搜索條件

Erland Sommarskog動態SQL的詛咒與祝福

如果您有正確的SQLServer 2008版本(SQL2008SP1CU5(10.0.2746)及更高版本)，您可以使用這個小技巧來實際使用索引：

加OPTION (RECOMPILE)在你的查詢中，見Erland的文章，而SQLServer將解析OR從內部(@LastName IS NULL OR LastName= @LastName)在根據局部變量的運行時值創建查詢計劃之前，可以使用索引。

這將適用于任何SQLServer版本(返回正確的結果)，但只有在SQL2008SP1CU5(10.0.2746)及更高版本上時才包括該選項(重新編譯)。該選項(重新編譯)將重新編譯您的查詢，只有列出的verison將根據局部變量的當前運行時值重新編譯它，這將為您提供最佳性能。如果不是在SQLServer 2008的那個版本上，只需離開這一行。

CREATE PROCEDURE spDoSearch    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = nullAS
    BEGIN
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END

反對回復 2019-07-05

呼啦一陣風

TA貢獻1802條經驗獲得超6個贊

“KM”的答案就其本身而言是好的，但未能完全貫徹他早期的一條建議；

.忽略緊湊型代碼，忽略對重復代碼的擔憂.

如果您希望獲得最佳的性能，那么您應該為每個可選條件的組合編寫一個定制查詢。這聽起來可能很極端，如果你有很多可選的標準，那么它可能是，但性能往往是努力和結果之間的權衡。在實踐中，可能有一組通用的參數組合，這些組合可以通過定制查詢進行目標，然后是針對所有其他組合的通用查詢(與其他答案一樣)。

CREATE PROCEDURE spDoSearch    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = nullASBEGIN

    IF (@FirstName IS NOT NULL AND @LastName IS NULL AND @Title IS NULL)
        -- Search by first name only
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
            FirstName = @FirstName    ELSE IF (@FirstName IS NULL AND @LastName IS NOT NULL AND @Title IS NULL)
        -- Search by last name only
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
            LastName = @LastName    ELSE IF (@FirstName IS NULL AND @LastName IS NULL AND @Title IS NOT NULL)
        -- Search by title only
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
            Title = @Title    ELSE IF (@FirstName IS NOT NULL AND @LastName IS NOT NULL AND @Title IS NULL)
        -- Search by first and last name
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
            FirstName = @FirstName            AND LastName = @LastName    ELSE
        -- Search by any other combination
        SELECT ID, FirstName, LastName, Title        FROM tblUsers        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))END

這種方法的優點是，在定制查詢處理的常見情況下，查詢是盡可能高效的-不受未提供的標準的影響。此外，索引和其他性能增強可以針對特定的定制查詢，而不是試圖滿足所有可能的情況。

反對回復 2019-07-05

互換的青春

TA貢獻1797條經驗獲得超6個贊

在以下情況下你可以這樣做，

CREATE PROCEDURE spDoSearch   @FirstName varchar(25) = null,
   @LastName varchar(25) = null,
   @Title varchar(25) = nullAS
  BEGIN
      SELECT ID, FirstName, LastName, Title      FROM tblUsers      WHERE
        (@FirstName IS NULL OR FirstName = @FirstName) AND
        (@LastNameName IS NULL OR LastName = @LastName) AND
        (@Title IS NULL OR Title = @Title)END

但是，有時更好地依賴于數據，創建動態查詢并執行它們。

反對回復 2019-07-05

3 回答
0 關注
1323 瀏覽

關注

添加回答

舉報

0/150

提交

取消

亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

熱搜

最近搜索清空

如何在T-SQL存儲過程中使用可選參數？

如何在T-SQL存儲過程中使用可選參數？

3 回答

添加回答