如何在T-SQL存储过程中使用可选参数？

Question

如何在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来完成这个任务，但我想避免这种情况。

Answer 1

基于给定参数的动态更改搜索是一个复杂的主题，通过另一种方式进行搜索，即使只有很小的差别，也会产生巨大的性能影响。关键是要使用索引，忽略紧凑的代码，忽略对重复代码的担忧，必须制定一个良好的查询执行计划(使用索引)。

阅读这篇文章，并考虑所有的方法。您的最佳方法将取决于您的参数、数据、模式和实际使用情况：

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

Answer 2

“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

这种方法的优点是，在定制查询处理的常见情况下，查询是尽可能高效的-不受未提供的标准的影响。此外，索引和其他性能增强可以针对特定的定制查询，而不是试图满足所有可能的情况。

Answer 3

在以下情况下你可以这样做，

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

但是，有时更好地依赖于数据，创建动态查询并执行它们。