¿Cómo puedo usar parámetros opcionales en un procedimiento almacenado de T-SQL?

La respuesta de @KM es buena hasta donde llega, pero no da seguimiento completo a uno de sus primeros consejos;

..., ignorar código compacto, ignorar preocuparse por repetir código, ...

Si está buscando lograr el mejor rendimiento, debe escribir una consulta a medida para cada posible combinación de criterios opcionales. Esto puede sonar extremo, y si tiene muchos criterios opcionales, entonces podría serlo, pero el rendimiento a menudo es una compensación entre esfuerzo y resultados. En la práctica, puede haber un conjunto común de combinaciones de parámetros que se pueden dirigir con consultas a medida, luego una consulta genérica (según las otras respuestas) para todas las demás combinaciones.

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

La ventaja de este enfoque es que en los casos comunes manejados por consultas a medida, la consulta es tan eficiente como puede ser - no hay impacto por los criterios no aplicados. Además, los índices y otras mejoras de rendimiento se pueden dirigir a consultas específicas a medida en lugar de que tratar de satisfacer todas las situaciones posibles.

Puede hacer en el siguiente caso,

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

Sin embargo dependen de los datos a veces mejor crear consultas dinámicas y ejecutarlas.

Extiende tu WHERE condición:

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, '') = '')

Es decir, combinar diferentes casos con condiciones booleanas.

Cinco años de retraso en la fiesta.

Se menciona en los enlaces proporcionados de la respuesta aceptada, pero creo que merece una respuesta explícita sobre la construcción dinámica de la consulta basada en los parámetros proporcionados. Por ejemplo:

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

Procedimiento

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

Uso

exec spDoSearch @TopCount = 3
exec spDoSearch @FirstName = 'Dick'

Ventajas:

• fácil de escribir y entender
• flexibilidad: genere fácilmente la consulta para filtraciones más complicadas (por ejemplo, dinámicas TOP)

Contras:

• posibles problemas de rendimiento en función de los parámetros, índices y volumen de datos proporcionados

No es una respuesta directa, sino relacionada con el problema, también conocido como el panorama general

Por lo general, estos procedimientos almacenados de filtrado no flotan alrededor, sino que se llaman desde alguna capa de servicio. Esto deja la opción de mover la lógica de negocio (filtrado) de SQL a la capa de servicio.

Un ejemplo es usar LINQ2SQL para generar la consulta basada en los filtros proporcionados:

    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;
    }

Ventajas:

• consulta generada dinámicamente basada en filtros proporcionados. No parámetro sniffing o recompile pistas necesarias
• algo más fácil de escribir para aquellos en el mundo OOP
• normalmente amigable con el rendimiento, ya que se emitirán consultas "simples" (sin embargo, aún se necesitan índices apropiados)

Contras:

• Se pueden alcanzar limitaciones de LINQ2QL y forzar un degradar a LINQ2Objects o volver a la solución SQL pura dependiendo del caso
• la escritura descuidada de LINQ podría generar consultas horribles (o muchas consultas, si se cargan las propiedades de navegación)

Esto también funciona:

    ...
    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))