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
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.
This question is related to
tsql
optional-parameters
Extend your WHERE
condition:
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, '') = '')
i. e. combine different cases with boolean conditions.
The answer from @KM is good as far as it goes but fails to fully follow up on one of his early bits of advice;
..., ignore compact code, ignore worrying about repeating code, ...
If you are looking to achieve the best performance then you should write a bespoke query for each possible combination of optional criteria. This might sound extreme, and if you have a lot of optional criteria then it might be, but performance is often a trade-off between effort and results. In practice, there might be a common set of parameter combinations that can be targeted with bespoke queries, then a generic query (as per the other answers) for all other combinations.
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
The advantage of this approach is that in the common cases handled by bespoke queries the query is as efficient as it can be - there's no impact by the unsupplied criteria. Also, indexes and other performance enhancements can be targeted at specific bespoke queries rather than trying to satisfy all possible situations.
You can do in the following case,
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
however depend on data sometimes better create dynamic query and execute them.
This also works:
...
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))
Five years late to the party.
It is mentioned in the provided links of the accepted answer, but I think it deserves an explicit answer on SO - dynamically building the query based on provided parameters. E.g.:
Setup
-- 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
Procedure
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
Usage
exec spDoSearch @TopCount = 3
exec spDoSearch @FirstName = 'Dick'
Pros:
Cons:
Not direct answer, but related to the problem aka the big picture
Usually, these filtering stored procedures do not float around, but are being called from some service layer. This leaves the option of moving away business logic (filtering) from SQL to service layer.
One example is using LINQ2SQL to generate the query based on provided filters:
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;
}
Pros:
Cons:
Source: Stackoverflow.com