如何在 T-SQL 存储过程中使用可选参数? [英] How can I use optional parameters in a T-SQL stored procedure?

问题描述

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

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:

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 的记录.如果在搜索参数中未指定标题，我想包括标题为 NULL 的记录 - 名字和姓氏相同.我知道我可能可以使用动态 SQL 做到这一点，但我想避免这种情况.

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.

推荐答案

基于给定参数动态更改搜索是一个复杂的主题，并且以一种方式进行，即使只有很小的差异，也会对性能产生巨大影响.关键是使用索引，忽略紧凑代码，忽略重复代码，一定要做好查询执行计划(使用索引).

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:

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

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

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

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:

将 OPTION (RECOMPILE) 添加到您的查询中，参见 Erland 的文章，SQL Server 会在查询计划基于局部变量的运行时值，可以使用索引.

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.

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

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.

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

这篇关于如何在 T-SQL 存储过程中使用可选参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！