Microsoft SQL Server - фильтрация по 4 переменным

Question

Как осуществлять фильтрацию по четырем переменным, при условии, что в некоторых может не быть значений (если переменная равна нулю, столбец должен отображать все данные)?

Строковой переменной "@lastName" можно присвоить '%' и тогда столбец будет выводить все данные, а как быть с переменными Int?

Declare @statusNumbers Int, @departmentNumbers Int, @postNumber Int, @lastName Varchar(100)

Set @statusNumbers = ?
Set @departmentNumbers = ?
Set @postNumber = ?
Set @lastName = '%'

Select Persons.Id, Persons.LastName, Persons.FirstName, Persons.SecondName, Statuses.Id, Statuses.Name as Status, Deps.Id, Deps.Name as Department, Posts.Id, Posts.Name as Post, Persons.DateEmploy as Employ, Persons.DateUnemploy as Unemploy
From dbo.Persons
Join dbo.Statuses on Statuses.Id = Persons.StatusId
Join dbo.Deps on Deps.Id = Persons.DepId
Join dbo.Posts on Posts.Id = Persons.PostId 
Where Persons.StatusId = @statusNumbers and 
    Persons.DepId = @departmentNumbers and 
    Persons.PostId = @postNumber and 
    LastName Like '%' + RTRIM(@lastName) + '%'

Согласно комментарию и ссылки, нужно было:

Инициализировать переменные:

Declare @statusNumbers Int = null, @departmentNumbers Int = null, @postNumber Int = null, @lastName Varchar(100) = '%'
Изменить условие в 'Where':

Where (Persons.StatusId = @statusNumbers or @statusNumbers is null) and (Persons.DepId = @departmentNumbers or @departmentNumbers is null) and (Persons.PostId = @postNumber or @postNumber is null) and LastName Like '%' + RTRIM(@lastName) + '%'

Проверив перебором переменных, все работает.

Answer 1

Простое(но не оптимальное) решение - это как в комментариях предложили:

(Persons.StatusId = @statusNumbers OR @statusNumbers IS NULL)

Но тогда фильтрация будет по всем указанным полям. А зачем искать по всем, если указано только одно(например)? Возникает ситуация, называемая "Kitchen Sink" - когда встанет вопрос оптимизации вашего запроса, то необходимо будет прибегнуть к динамическому SQL и сделать так(а лучше сразу):

DECLARE @statusNumbers     INT = 1
DECLARE @departmentNumbers INT = NULL
DECLARE @postNumber        INT = 3
DECLARE @lastName          VARCHAR(100) = 'Zlobin'
    
DECLARE @script VARCHAR(MAX) = '
SELECT  Persons.Id,
        Persons.LastName,
        Persons.FirstName,
        Persons.SecondName,
        Statuses.Id,
        Statuses.Name AS Status,
        Deps.Id,
        Deps.Name AS Department,
        Posts.Id,
        Posts.Name AS Post,
        Persons.DateEmploy AS Employ,
        Persons.DateUnemploy AS Unemploy
FROM dbo.Persons
JOIN dbo.Statuses ON Statuses.Id = Persons.StatusId
JOIN dbo.Deps ON Deps.Id = Persons.DepId
JOIN dbo.Posts ON Posts.Id = Persons.PostId 
WHERE 1 = 1 '

IF @statusNumbers       IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.StatusId = ', @statusNumbers, ' ')
IF @departmentNumbers   IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.DepId = ', @departmentNumbers, ' ')
IF @postNumber          IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.PostId = ', @postNumber, ' ')
IF @lastName            IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.LastName LIKE ''%', TRIM(@lastName), '%'' ')

PRINT(@script) --Посмотреть текст запроса
EXEC(@script) --Выполнить текст запроса

Answer 2

Учитывая советы по "Kitchen Sink" и "sp_executesql", получается:

DECLARE @statusNumbers     INT = NULL
DECLARE @departmentNumbers INT = NULL
DECLARE @postNumber        INT = NULL
DECLARE @lastName          NVARCHAR(MAX) = NULL

DECLARE @script NVARCHAR(MAX) = '
SELECT  Persons.Id,
        Persons.LastName,
        Persons.FirstName,
        Persons.SecondName,
        Statuses.Id,
        Statuses.Name AS Status,
        Deps.Id,
        Deps.Name AS Department,
        Posts.Id,
        Posts.Name AS Post,
        Persons.DateEmploy AS Employ,
        Persons.DateUnemploy AS Unemploy
FROM dbo.Persons
JOIN dbo.Statuses ON Statuses.Id = Persons.StatusId
JOIN dbo.Deps ON Deps.Id = Persons.DepId
JOIN dbo.Posts ON Posts.Id = Persons.PostId 
WHERE 1 = 1'

+ CASE WHEN @statusNumbers       IS NOT NULL THEN ' AND Persons.StatusId = @statusNumbers' ELSE '' END
+ CASE WHEN @departmentNumbers   IS NOT NULL THEN ' AND Persons.DepId = @departmentNumbers' ELSE '' END
+ CASE WHEN @postNumber          IS NOT NULL THEN ' AND Persons.PostId = @postNumber' ELSE '' END
+ CASE WHEN @lastName            IS NOT NULL THEN ' AND Persons.LastName LIKE @lastName' ELSE '' END

DECLARE @params NVARCHAR(MAX) = '
@statusNumbers     INT,
@departmentNumbers INT,
@postNumber        INT,
@lastName          NVARCHAR(100)'

EXEC sp_executesql @script, @params,
@statusNumbers,
@departmentNumbers,
@postNumber,
@lastName

Только не знаю, есть ли разница между:

IF @statusNumbers       IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.StatusId = ', @statusNumbers, ' ')

Или

+ CASE WHEN @statusNumbers       IS NOT NULL THEN ' AND Persons.StatusId = @statusNumbers' ELSE '' END

БЛОГ НА HUSL

Microsoft SQL Server - фильтрация по 4 переменным

Ответы (2 шт):