如何在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&nbsp;PROCEDURE&nbsp;spDoSearch&nbsp;&nbsp;&nbsp;&nbsp;@FirstName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;&nbsp;@LastName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;&nbsp;@Title&nbsp;varchar(25)&nbsp;=&nbsp;nullAS &nbsp;&nbsp;&nbsp;&nbsp;BEGIN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@FirstName&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(FirstName&nbsp;=&nbsp;@FirstName)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(@LastName&nbsp;&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(LastName&nbsp;&nbsp;=&nbsp;@LastName&nbsp;)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(@Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;@Title&nbsp;&nbsp;&nbsp;&nbsp;)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OPTION&nbsp;(RECOMPILE)&nbsp;---<<<<use&nbsp;if&nbsp;on&nbsp;for&nbsp;SQL&nbsp;2008&nbsp;SP1&nbsp;CU5&nbsp;(10.0.2746)&nbsp;and&nbsp;later &nbsp;&nbsp;&nbsp;&nbsp;END

Answer 2

“KM”的答案就其本身而言是好的，但未能完全贯彻他早期的一条建议；.忽略紧凑型代码，忽略对重复代码的担忧.如果您希望获得最佳的性能，那么您应该为每个可选条件的组合编写一个定制查询。这听起来可能很极端，如果你有很多可选的标准，那么它可能是，但性能往往是努力和结果之间的权衡。在实践中，可能有一组通用的参数组合，这些组合可以通过定制查询进行目标，然后是针对所有其他组合的通用查询(与其他答案一样)。CREATE&nbsp;PROCEDURE&nbsp;spDoSearch&nbsp;&nbsp;&nbsp;&nbsp;@FirstName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;&nbsp;@LastName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;&nbsp;@Title&nbsp;varchar(25)&nbsp;=&nbsp;nullASBEGIN &nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(@FirstName&nbsp;IS&nbsp;NOT&nbsp;NULL&nbsp;AND&nbsp;@LastName&nbsp;IS&nbsp;NULL&nbsp;AND&nbsp;@Title&nbsp;IS&nbsp;NULL) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;Search&nbsp;by&nbsp;first&nbsp;name&nbsp;only &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FirstName&nbsp;=&nbsp;@FirstName&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;IF&nbsp;(@FirstName&nbsp;IS&nbsp;NULL&nbsp;AND&nbsp;@LastName&nbsp;IS&nbsp;NOT&nbsp;NULL&nbsp;AND&nbsp;@Title&nbsp;IS&nbsp;NULL) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;Search&nbsp;by&nbsp;last&nbsp;name&nbsp;only &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LastName&nbsp;=&nbsp;@LastName&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;IF&nbsp;(@FirstName&nbsp;IS&nbsp;NULL&nbsp;AND&nbsp;@LastName&nbsp;IS&nbsp;NULL&nbsp;AND&nbsp;@Title&nbsp;IS&nbsp;NOT&nbsp;NULL) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;Search&nbsp;by&nbsp;title&nbsp;only &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Title&nbsp;=&nbsp;@Title&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;IF&nbsp;(@FirstName&nbsp;IS&nbsp;NOT&nbsp;NULL&nbsp;AND&nbsp;@LastName&nbsp;IS&nbsp;NOT&nbsp;NULL&nbsp;AND&nbsp;@Title&nbsp;IS&nbsp;NULL) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;Search&nbsp;by&nbsp;first&nbsp;and&nbsp;last&nbsp;name &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FirstName&nbsp;=&nbsp;@FirstName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;LastName&nbsp;=&nbsp;@LastName&nbsp;&nbsp;&nbsp;&nbsp;ELSE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;Search&nbsp;by&nbsp;any&nbsp;other&nbsp;combination &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@FirstName&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(FirstName&nbsp;=&nbsp;@FirstName)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(@LastName&nbsp;&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(LastName&nbsp;&nbsp;=&nbsp;@LastName&nbsp;)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(@Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;(Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;@Title&nbsp;&nbsp;&nbsp;&nbsp;))END这种方法的优点是，在定制查询处理的常见情况下，查询是尽可能高效的-不受未提供的标准的影响。此外，索引和其他性能增强可以针对特定的定制查询，而不是试图满足所有可能的情况。

Answer 3

在以下情况下你可以这样做，CREATE&nbsp;PROCEDURE&nbsp;spDoSearch&nbsp;&nbsp;&nbsp;@FirstName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;@LastName&nbsp;varchar(25)&nbsp;=&nbsp;null, &nbsp;&nbsp;&nbsp;@Title&nbsp;varchar(25)&nbsp;=&nbsp;nullAS &nbsp;&nbsp;BEGIN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;ID,&nbsp;FirstName,&nbsp;LastName,&nbsp;Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;tblUsers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@FirstName&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;FirstName&nbsp;=&nbsp;@FirstName)&nbsp;AND &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@LastNameName&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;LastName&nbsp;=&nbsp;@LastName)&nbsp;AND &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@Title&nbsp;IS&nbsp;NULL&nbsp;OR&nbsp;Title&nbsp;=&nbsp;@Title)END但是，有时更好地依赖于数据，创建动态查询并执行它们。