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

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt SomeServiceModel gt  GetServiceModels CustomFilter filters                var query   DataAccess SomeRepository AllNoTracking              partial and insensitive search          if   string IsNullOrWhiteSpace filters SomeName               query   query Where item   gt  item SomeName IndexOf filters SomeName  StringComparison OrdinalIgnoreCase     -1              filter by multiple selection         if   filters CreatedByList  Count    0   gt  0              query   query Where item   gt  filters CreatedByList Contains item CreatedById            if  filters EnabledOnly              query   query Where item   gt  item IsEnabled            var modelList   query ToList            var serviceModelList   MappingService MapEx lt SomeDataModel  SomeServiceModel gt  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

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

Examples related to tsql

Examples related to optional-parameters