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

I am creating a stored procedure to do a search through a table. I have many different search fields, all of which are optional. Is there a way to create a stored procedure that will handle this? Let's say I have a table with four fields: ID, FirstName, LastName and Title. I could do something like this:

我正在创建一个存储过程，以便通过一个表进行搜索。我有许多不同的搜索字段，所有这些都是可选的。是否有办法创建一个存储过程来处理这个问题?假设有一个包含四个字段的表: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

This sort of works. However it ignores records where FirstName, LastName or Title are NULL. If Title is not specified in the search parameters I want to include records where Title is NULL - same for FirstName and LastName. I know I could probably do this with dynamic SQL but I would like to avoid that.

这类作品。但是，它忽略了FirstName、LastName或Title为空的记录。如果在搜索参数中没有指定标题，我希望包含标题为NULL的记录，名称和LastName相同。我知道我可以用动态SQL来做，但是我想避免这样做。

6 个解决方案

#1

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

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

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

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

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

通过Erland Sommarskog对T-SQL的动态搜索条件。

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

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

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

如果您有合适的SQL Server 2008版本(SQL 2008 SP1 CU5(10.0.2746)和稍后)，您可以使用这个小技巧来实际使用索引:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

在您的查询中添加选项(重新编译)，查看Erland的文章，SQL Server将在基于本地变量的运行时值创建查询计划之前(@LastName是NULL或LastName= @LastName)来解析或从内部解析(@LastName = @LastName)，并且可以使用索引。

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

这将适用于任何SQL Server版本(返回适当的结果)，但只包括在SQL 2008 SP1 CU5(10.0.2746)和之后的选项(重新编译)。这个选项(RECOMPILE)将重新编译您的查询，仅列出的verison将根据本地变量的当前运行时值重新编译它，这将使您获得最佳性能。如果不是在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

#2

The answer from @KM is good as far as it goes but fails to fully follow up on one of his early bits of advice;

从@KM的角度来看，答案是好的，但不能完全按照他早期的建议去做;

..., ignore compact code, ignore worrying about repeating code, ...

…，忽略紧凑的代码，忽略对重复代码的担忧，……

If you are looking to achieve the best performance then you should write a bespoke query for each possible combination of optional criteria. This might sound extreme, and if you have a lot of optional criteria then it might be, but performance is often a trade-off between effort and results. In practice, there might be a common set of parameter combinations that can be targeted with bespoke queries, then a generic query (as per the other answers) for all other combinations.

如果您希望获得最佳性能，那么您应该为每个可能的可选标准组合编写一个定制的查询。这可能听起来很极端，如果您有很多可选的标准，那么它可能是，但是性能常常是努力和结果之间的权衡。在实践中，可能会有一组通用的参数组合，这些组合可以针对定制的查询，然后是针对所有其他组合的通用查询(如其他答案)。

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

    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

The advantage of this approach is that in the common cases handled by bespoke queries the query is as efficient as it can be - there's no impact by the unsupplied criteria. Also, indexes and other performance enhancements can be targeted at specific bespoke queries rather than trying to satisfy all possible situations.

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

#3

You can do in the following case,

你可以这样做，

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
        (@LastNameName IS NULL OR LastName = @LastName) AND
        (@Title IS NULL OR Title = @Title)
END

however depend on data sometimes better create dynamic query and execute them.

然而，依赖数据有时会更好地创建动态查询并执行它们。

#4

Extend your WHERE condition:

扩展你的条件:

WHERE
    (FirstName = ISNULL(@FirstName, FirstName)
    OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
    OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
    OR COALESCE(@Title, Title, '') = '')

i. e. combine different cases with boolean conditions.

将不同的情况与布尔条件结合起来。

#5

Five years late to the party.

聚会迟到了五年。

It is mentioned in the provided links of the accepted answer, but I think it deserves an explicit answer on SO - dynamically building the query based on provided parameters. E.g.:

在已接受的答案的链接中提到了它，但是我认为它应该得到一个明确的答案——基于提供的参数动态构建查询。例如:

Setup

设置

-- drop table Person
create table Person
(
    PersonId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Person PRIMARY KEY,
    FirstName NVARCHAR(64) NOT NULL,
    LastName NVARCHAR(64) NOT NULL,
    Title NVARCHAR(64) NULL
)
GO

INSERT INTO Person (FirstName, LastName, Title)
VALUES ('Dick', 'Ormsby', 'Mr'), ('Serena', 'Kroeger', 'Ms'), 
    ('Marina', 'Losoya', 'Mrs'), ('Shakita', 'Grate', 'Ms'), 
    ('Bethann', 'Zellner', 'Ms'), ('Dexter', 'Shaw', 'Mr'),
    ('Zona', 'Halligan', 'Ms'), ('Fiona', 'Cassity', 'Ms'),
    ('Sherron', 'Janowski', 'Ms'), ('Melinda', 'Cormier', 'Ms')
GO

Procedure

过程

ALTER PROCEDURE spDoSearch
    @FirstName varchar(64) = null,
    @LastName varchar(64) = null,
    @Title varchar(64) = null,
    @TopCount INT = 100
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000) = '
        SELECT TOP ' + CAST(@TopCount AS VARCHAR) + ' *
        FROM Person
        WHERE 1 = 1'

    PRINT @SQL

    IF (@FirstName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @FirstName'
    IF (@LastName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @LastName'
    IF (@Title IS NOT NULL) SET @SQL = @SQL + ' AND Title = @Title'

    EXEC sp_executesql @SQL, N'@TopCount INT, @FirstName varchar(25), @LastName varchar(25), @Title varchar(64)', 
         @TopCount, @FirstName, @LastName, @Title
END
GO

Usage

使用

exec spDoSearch @TopCount = 3
exec spDoSearch @FirstName = 'Dick'

Pros:

优点:

• easy to write and understand
• 易于编写和理解。
• flexibility - easily generate the query for trickier filterings (e.g. dynamic TOP)
• 灵活性——容易为更复杂的过滤(例如动态顶部)生成查询

Cons:

缺点:

• possible performance problems depending on provided parameters, indexes and data volume
• 可能的性能问题取决于提供的参数、索引和数据量。

Not direct answer, but related to the problem aka the big picture

不是直接的回答，而是与问题相关的大局。

Usually, these filtering stored procedures do not float around, but are being called from some service layer. This leaves the option of moving away business logic (filtering) from SQL to service layer.

通常，这些过滤存储过程不会四处浮动，而是从某个服务层调用。这样就可以选择将业务逻辑(过滤)从SQL移到服务层。

One example is using LINQ2SQL to generate the query based on provided filters:

一个例子是使用LINQ2SQL根据提供的过滤器生成查询:

    public IList<SomeServiceModel> GetServiceModels(CustomFilter filters)
    {
        var query = DataAccess.SomeRepository.AllNoTracking;

        // partial and insensitive search 
        if (!string.IsNullOrWhiteSpace(filters.SomeName))
            query = query.Where(item => item.SomeName.IndexOf(filters.SomeName, StringComparison.OrdinalIgnoreCase) != -1);
        // filter by multiple selection
        if ((filters.CreatedByList?.Count ?? 0) > 0)
            query = query.Where(item => filters.CreatedByList.Contains(item.CreatedById));
        if (filters.EnabledOnly)
            query = query.Where(item => item.IsEnabled);

        var modelList = query.ToList();
        var serviceModelList = MappingService.MapEx<SomeDataModel, SomeServiceModel>(modelList);
        return serviceModelList;
    }

Pros:

优点:

• dynamically generated query based on provided filters. No parameter sniffing or recompile hints needed
• 基于提供的过滤器动态生成的查询。不需要任何参数嗅探或重新编译提示。
• somewhat easier to write for those in the OOP world
• 在OOP的世界里，写起来更容易些。
• typically performance friendly, since "simple" queries will be issued (appropriate indexes are still needed though)
• 通常性能友好，因为将发出“简单”查询(但仍然需要适当的索引)

Cons:

缺点:

• LINQ2QL limitations may be reached and forcing a downgrade to LINQ2Objects or going back to pure SQL solution depending on the case
• 可以达到LINQ2QL限制，并强制降级到LINQ2Objects，或者根据情况返回纯SQL解决方案。
• careless writing of LINQ might generate awful queries (or many queries, if navigation properties loaded)
• LINQ的粗心编写可能会生成糟糕的查询(或许多查询，如果装载的导航属性)

#6

This also works:

这同样适用:

    ...
    WHERE
        (FirstName IS NULL OR FirstName = ISNULL(@FirstName, FirstName)) AND
        (LastName IS NULL OR LastName = ISNULL(@LastName, LastName)) AND
        (Title IS NULL OR Title = ISNULL(@Title, Title))