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

我正在创建一个存储过程来对表进行搜索。我有许多不同的搜索字段，所有这些都是可选的。有没有一种方法可以创建一个存储过程来处理这个问题？假设我有一个包含四个字段的表：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为空的记录。如果没有在搜索参数中指定title，我希望包括title为空的记录-FirstName和LastName相同。我知道我可以用动态SQL做到这一点，但我想避免这种情况。

363679 次浏览
小开
最佳答案

根据给定的参数动态改变搜索是一个复杂的问题，即使只有非常微小的差异，也会对性能产生巨大的影响。关键是使用索引，忽略紧凑代码，忽略担心重复代码，你必须制定一个良好的查询执行计划（使用索引）。

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

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

Erland Sommarskog的《动态SQL的诅咒与祝福》

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

将OPTION (RECOMPILE)添加到查询中，见厄兰的文章，SQL Server将在基于局部变量的运行时值创建查询计划之前，从(@LastName IS NULL OR LastName= @LastName)中解析OR，并且可以使用索引。

这适用于任何SQL Server版本（返回正确的结果），但如果您使用的是SQL 2008 SP1 CU5（10.0.2746）及更高版本，则仅包括选项（重新编译）。选项（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
小开

您可以在以下情况下执行此操作，

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

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

小开

延长WHERE条件：

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, '') = '')

我。E.用布尔条件组合不同的情况。

小开

这也是可行的：

    ...
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))
小开

就目前而言，@KM的回答是好的，但未能完全遵循他早期的一条建议。

..，忽略紧凑的代码，忽略担心重复的代码，..

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

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

这种方法的优点是，在定制查询处理的常见情况下，查询尽可能高效-不受未提供的条件的影响。此外，索引和其他性能增强可以针对特定的定制查询，而不是试图满足所有可能的情况。

小开

迟到了五年。

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

设置

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

程序

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

用法

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

优点：

  • 易于编写和理解
  • 灵活性-轻松生成更复杂的过滤查询（例如动态TOP）

缺点：

  • 可能出现的性能问题取决于所提供的参数、索引和数据量

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

通常，这些筛选存储过程不会到处浮动，而是从某个服务层调用。这就留下了将业务逻辑（过滤）从SQL转移到服务层的选项。

一个示例是使用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;
}

优点：

  • 基于提供的筛选器动态生成的查询。不需要参数嗅探或重新编译提示
  • 对于OOP领域的人来说，编写起来更容易一些。
  • 通常是性能友好的，因为将发出“简单”查询（但仍然需要适当的索引）

缺点：

  • 根据具体情况，可能会达到LINQ2QL限制，并强制降级到LINQ2Objects或返回到纯SQL解决方案
  • 不小心编写LINQ可能会生成糟糕的查询（如果加载了导航属性，则会生成许多查询）

提交答案