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

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)