tsql - How can I use optional parameters in a T-SQL stored procedure?

ID : 20146

viewed : 33

Tags : tsqloptional-parameterstsql

Top 5 Answer for tsql - How can I use optional parameters in a T-SQL stored procedure?

vote vote

99

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:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

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:

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.

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

90

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.

vote vote

78

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.

vote vote

60

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:

  • easy to write and understand
  • flexibility - easily generate the query for trickier filterings (e.g. dynamic TOP)

Cons:

  • possible performance problems depending on provided parameters, indexes and data volume

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:

  • dynamically generated query based on provided filters. No parameter sniffing or recompile hints needed
  • somewhat easier to write for those in the OOP world
  • typically performance friendly, since "simple" queries will be issued (appropriate indexes are still needed though)

Cons:

  • LINQ2QL limitations may be reached and forcing a downgrade to LINQ2Objects or going back to pure SQL solution depending on the case
  • careless writing of LINQ might generate awful queries (or many queries, if navigation properties loaded)
vote vote

55

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.

Top 3 video Explaining tsql - How can I use optional parameters in a T-SQL stored procedure?

Related QUESTION?