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