首頁猿問如何在T-SQL存儲(chǔ)過程中使用可選參數(shù)？

如何在T-SQL存儲(chǔ)過程中使用可選參數(shù)？

MySQL SQL Server

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

如何在T-SQL存儲(chǔ)過程中使用可選參數(shù)？我正在創(chuàng)建一個(gè)存儲(chǔ)過程來對(duì)表進(jìn)行搜索。我有許多不同的搜索字段，所有這些都是可選的。是否有一種方法可以創(chuàng)建一個(gè)存儲(chǔ)過程來處理這個(gè)問題？假設(shè)我有一個(gè)包含四個(gè)字段的表：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的記錄。如果沒有在搜索參數(shù)中指定title，我希望包括標(biāo)題為NULL的記錄-對(duì)于FirstName和LastName來說是相同的。我知道我可能可以使用動(dòng)態(tài)SQL來完成這個(gè)任務(wù)，但我想避免這種情況。

查看完整描述

3 回答

慕少森

TA貢獻(xiàn)2019條經(jīng)驗(yàn) 獲得超9個(gè)贊

基于給定參數(shù)的動(dòng)態(tài)更改搜索是一個(gè)復(fù)雜的主題，通過另一種方式進(jìn)行搜索，即使只有很小的差別，也會(huì)產(chǎn)生巨大的性能影響。關(guān)鍵是要使用索引，忽略緊湊的代碼，忽略對(duì)重復(fù)代碼的擔(dān)憂，必須制定一個(gè)良好的查詢執(zhí)行計(jì)劃(使用索引)。

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

Erland Sommarskog在T-SQL中的動(dòng)態(tài)搜索條件

Erland Sommarskog動(dòng)態(tài)SQL的詛咒與祝福

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

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

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

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

反對(duì) 回復(fù) 2019-07-05

呼啦一陣風(fēng)

TA貢獻(xiàn)1802條經(jīng)驗(yàn) 獲得超6個(gè)贊

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

.忽略緊湊型代碼，忽略對(duì)重復(fù)代碼的擔(dān)憂.

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

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

這種方法的優(yōu)點(diǎn)是，在定制查詢處理的常見情況下，查詢是盡可能高效的-不受未提供的標(biāo)準(zhǔn)的影響。此外，索引和其他性能增強(qiáng)可以針對(duì)特定的定制查詢，而不是試圖滿足所有可能的情況。

反對(duì) 回復(fù) 2019-07-05

互換的青春

TA貢獻(xiàn)1797條經(jīng)驗(yàn) 獲得超6個(gè)贊

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

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

但是，有時(shí)更好地依賴于數(shù)據(jù)，創(chuàng)建動(dòng)態(tài)查詢并執(zhí)行它們。

反對(duì) 回復(fù) 2019-07-05