[tsql] T-SQL 저장 프로 시저에서 선택적 매개 변수를 사용하려면 어떻게해야합니까?

파티에 5 년 늦었 어

허용 된 답변의 제공된 링크에 언급되어 있지만 SO에 대한 명시 적 답변이 필요하다고 생각합니다. 제공 된 매개 변수를 기반으로 쿼리를 동적으로 작성합니다. 예 :

설정

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

장점 :

• 작성하고 이해하기 쉽다
• 유연성-까다로운 필터링에 대한 쿼리를 쉽게 생성 (예 : 동적 TOP)

단점 :

• 제공된 매개 변수, 색인 및 데이터 볼륨에 따라 가능한 성능 문제

직접적인 대답은 아니지만 큰 그림으로 알려진 문제와 관련이 있습니다.

일반적으로 이러한 필터링 저장 프로시 저는 플로팅되지 않지만 일부 서비스 계층에서 호출됩니다. 이것은 비즈니스 로직 (필터링)을 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;
    }

장점 :

• 제공된 필터를 기반으로 동적으로 생성 된 쿼리 매개 변수 스니핑 또는 재 컴파일 힌트 필요 없음
• OOP 세계의 사람들을 위해 작성하기가 다소 쉬움
• “간단한”쿼리가 발행되므로 일반적으로 성능 친화적입니다 (적절한 인덱스가 여전히 필요함)

단점 :

• LINQ2QL 제한에 도달하고 경우에 따라 LINQ2Object로 다운 그레이드하거나 강제 SQL 솔루션으로 되돌아 갈 수 있음
• 부주의 한 LINQ 작성은 끔찍한 쿼리 (또는 탐색 속성이로드 된 경우 많은 쿼리)를 생성 할 수 있습니다.

답변

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

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