Как я могу использовать необязательные параметры в хранимой процедуре T-SQL?

ответ от @KM хорош, насколько это возможно, но не может полностью следовать одному из его ранних советов;

..., игнорировать компактный код, игнорировать беспокойство о повторении кода,...

Если вы хотите достичь наилучшей производительности, то вы должны написать заказной запрос для каждой возможной комбинации необязательных критериев. Это может показаться экстремальным, и если у вас есть много дополнительных критериев, то это может быть, но производительность часто является компромисс между усилиями и результатами. На практике может быть общий набор комбинаций параметров, которые могут быть нацелены на заказные запросы, а затем общий запрос (согласно другим ответам) для всех других комбинаций.

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

преимущество этого подхода заключается в том, что в обычных случаях, обрабатываемых заказными запросами, запрос настолько эффективен, насколько это возможно - нет никакого влияния на неподписанные критерии. Кроме того, индексы и другие улучшения производительности могут быть направлены на конкретные индивидуальные запросы, а не пытаться удовлетворить все возможные ситуации.

вы можете сделать в следующем случае

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

однако в зависимости от данных иногда лучше создавать динамические запросы и выполнять их.

продлить WHERE состояние:

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

т. е. объединить различные случаи с булевыми условиями.

на пять лет опоздал на вечеринку.

он упоминается в предоставленных ссылках принятого ответа, но я думаю, что он заслуживает явного ответа на так называемое динамическое построение запроса на основе предоставленных параметров. Например:

настройка

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

процедура

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

использование

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

плюсы:

• легко писать и понимать
• гибкость-легко генерировать запрос на более сложные фильтрации (например, динамическая вершина)

плюсы:

• возможные проблемы с производительностью в зависимости от предоставленных параметров, индексов и данных объемом

не прямой ответ, но связанный с проблемой ака большая картина

обычно эти фильтрующие хранимые процедуры не плавают, а вызываются из некоторого уровня сервиса. Этот оставляет возможность перемещения бизнес-логики (фильтрации) из SQL на уровень сервиса.

одним из примеров является использование LINQ2SQL для создания запроса на основе предоставленных фильтров:

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

плюсы:

• динамически генерируемый запрос на основе предоставленных фильтров. Нет нюхают параметр или перекомпиляция подсказки нужны
• несколько легче писать для тех, кто в мире ООП
• обычно производительность дружественный, так как" простые " запросы будут выдаваться (соответствующие индексы все еще необходимы)

плюсы:

• ограничения LINQ2QL могут быть достигнуты и заставить понизиться до LINQ2Objects или вернуться к чистому решению SQL в зависимости от случая
• небрежное написание LINQ может генерировать ужасные запросы (или много запросов, если свойства навигации загружены)

Это также работает:

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