Jak mogę użyć opcjonalnych parametrów w procedurze przechowywanej T-SQL?

Tworzę procedurę przechowywaną do przeszukiwania tabeli. Mam wiele różnych pól wyszukiwania, z których wszystkie są opcjonalne. Czy istnieje sposób na utworzenie procedury składowanej, która sobie z tym poradzi? Powiedzmy, że mam tabelę z czterema polami: ID, Imię, Nazwisko i Tytuł. Mógłbym zrobić coś takiego:

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

Ten rodzaj działa. Jednak ignoruje rekordy, w których FirstName, LastName lub Title mają wartość NULL. Jeśli tytuł nie jest określony w parametrach wyszukiwania, chcę dołączyć rekordy, w których tytuł ma wartość NULL - to samo dla FirstName i LastName. Wiem, że prawdopodobnie mógłbym to zrobić za pomocą dynamicznego SQL, ale chciałbym tego uniknąć.

Dynamicznie zmieniające się wyszukiwania oparte na danych parametrach to skomplikowany temat, a robienie tego w jedną stronę, nawet z niewielką różnicą, może mieć ogromny wpływ na wydajność. Kluczem jest użycie indeksu, zignorowanie zwartego kodu, zignorowanie obaw związanych z powtarzaniem kodu, należy przygotować dobry plan wykonania zapytania (użyć indeksu).

Przeczytaj to i rozważ wszystkie metody. Twoja najlepsza metoda będzie zależeć od parametrów, danych, schematu i faktycznego użycia:

Warunki dynamicznego wyszukiwania w T-SQL autorstwa Erlanda Sommarskoga

Klątwa i błogosławieństwa dynamicznego SQL autorstwa Erlanda Sommarskoga

Jeśli masz odpowiednią wersję programu SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) i nowszy), możesz użyć tej małej sztuczki, aby faktycznie użyć indeksu:

Dodaj OPTION (RECOMPILE)do zapytania, zobacz artykuł Erlanda , a SQL Server rozwiąże ORod wewnątrz, (@LastName IS NULL OR LastName= @LastName)zanim zostanie utworzony plan zapytań na podstawie wartości środowiska wykonawczego zmiennych lokalnych i można będzie użyć indeksu.

Będzie to działać dla dowolnej wersji programu SQL Server (zwróć prawidłowe wyniki), ale uwzględniaj OPCJĘ (RECOMPILE), jeśli korzystasz z SQL 2008 SP1 CU5 (10.0.2746) i nowszych. OPCJA (RECOMPILE) ponownie skompiluje zapytanie, tylko wymieniona wersja zweryfikuje je na podstawie bieżących wartości zmiennych lokalnych w czasie wykonywania, co zapewni najlepszą wydajność. Jeśli nie w tej wersji SQL Server 2008, po prostu pozostaw tę linię wyłączoną.

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

Odpowiedź od @KM jest dobra, o ile to możliwe, ale nie w pełni odpowiada jednej z jego wczesnych rad;

..., ignoruj ​​zwarty kod, ignoruj ​​martwienie się o powtarzanie kodu, ...

Jeśli chcesz osiągnąć najlepszą wydajność, powinieneś napisać indywidualne zapytanie dla każdej możliwej kombinacji kryteriów opcjonalnych. Może to zabrzmieć ekstremalnie, a jeśli masz wiele opcjonalnych kryteriów, może być, ale wydajność jest często kompromisem między wysiłkiem a wynikami. W praktyce może istnieć wspólny zestaw kombinacji parametrów, które mogą być kierowane za pomocą niestandardowych zapytań, a następnie ogólne zapytanie (jak w przypadku innych odpowiedzi) dla wszystkich innych kombinacji.

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

Zaletą tego podejścia jest to, że w typowych przypadkach obsługiwanych przez niestandardowe zapytania zapytanie jest tak wydajne, jak to tylko możliwe - nie ma wpływu na niespełnione kryteria. Ponadto indeksy i inne ulepszenia wydajności mogą być ukierunkowane na określone zapytania na zamówienie, a nie próbować zaspokoić wszystkie możliwe sytuacje.

Możesz to zrobić w następującym przypadku:

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

jednak zależą od danych, lepiej lepiej utworzyć dynamiczne zapytanie i wykonać je.

Pięć lat spóźnienia na przyjęcie.

Jest o tym mowa w podanych linkach akceptowanej odpowiedzi, ale myślę, że zasługuje na jednoznaczną odpowiedź na SO - dynamiczne budowanie zapytania w oparciu o podane parametry. Na przykład:

Ustawiać

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

Procedura

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

Stosowanie

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

Plusy:

• łatwe do napisania i zrozumienia
• elastyczność - łatwe generowanie zapytania o trudniejsze filtrowanie (np. dynamiczne TOP)

Cons:

• możliwe problemy z wydajnością w zależności od podanych parametrów, indeksów i ilości danych

Nie bezpośrednia odpowiedź, ale związana z problemem, czyli dużym obrazem

Zazwyczaj te przechowywane procedury filtrowania nie są zmiennoprzecinkowe, ale są wywoływane z jakiejś warstwy usług. Pozostawia to opcję przeniesienia logiki biznesowej (filtrowania) z SQL do warstwy usługi.

Jednym z przykładów jest użycie LINQ2SQL do wygenerowania zapytania w oparciu o dostarczone filtry:

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

Plusy:

• dynamicznie generowane zapytanie w oparciu o dostarczone filtry. Nie ma potrzeby wyszukiwania parametrów ani wskazówek dotyczących ponownej kompilacji
• nieco łatwiej pisać dla osób ze świata OOP
• zazwyczaj sprzyja wydajności, ponieważ będą wydawane „proste” zapytania (jednak potrzebne są odpowiednie indeksy)

Cons:

• Można osiągnąć ograniczenia LINQ2QL i wymuszać przejście na LINQ2Objects lub powrót do czystego rozwiązania SQL, w zależności od przypadku
• nieostrożne pisanie LINQ może generować okropne zapytania (lub wiele zapytań, jeśli załadowane zostaną właściwości nawigacji)

Przedłuż swój WHEREstan:

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

tj. łącz różne przypadki z warunkami logicznymi.

Działa to również:

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