如何在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将根据局部变量的运行时值在创建查询计划之前从(@LastName IS NULL OR LastName= @LastName)中解析OR，并且可以使用索引.

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屋！