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

小编典典

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

all

我正在创建一个存储过程来通过表进行搜索。我有许多不同的搜索字段，所有这些都是可选的。有没有办法创建一个存储过程来处理这个问题？假设我有一个包含四个字段的表：ID、FirstName、LastName
和 Title。我可以做这样的事情：

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    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，我想包含
Title 为 NULL 的记录 - FirstName 和 LastName 相同。我知道我可能可以使用动态 SQL 来做到这一点，但我想避免这种情况。

阅读 90

2022-07-16

共1个答案

小编典典

基于给定参数动态更改搜索是一个复杂的主题，并且以一种方式而不是另一种方式进行搜索，即使只有非常微小的差异，也会对性能产生巨大影响。关键是使用索引，忽略紧凑代码，忽略重复代码的担心，必须制定好的查询执行计划（使用索引）。

阅读本文并考虑所有方法。您的最佳方法将取决于您的参数、数据、架构和实际使用情况：

Erland Sommarskog 在 T-SQL 中的动态搜索条件

Erland Sommarskog 的动态 SQL 的诅咒和祝福

如果您有正确的 SQL Server 2008 版本（SQL 2008 SP1 CU5 (10.0.2746)
及更高版本），您可以使用这个小技巧来实际使用索引：

添加OPTION (RECOMPILE)到您的查询中，请参阅 Erland 的文章，SQL Server 将在根据本地变量的运行时值创建查询计划之前OR从内部解析(@LastName IS NULL OR LastName= @LastName)，并且可以使用索引。

这适用于任何 SQL Server 版本（返回正确的结果），但如果您使用的是 SQL 2008 SP1 CU5 (10.0.2746) 及更高版本，则仅包括
OPTION(RECOMPILE)。OPTION(RECOMPILE)
将重新编译您的查询，只有列出的版本会根据本地变量的当前运行时值重新编译它，这将为您提供最佳性能。如果不在该版本的 SQL Server 2008
上，请取消该行。

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    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

2022-07-16