Время от времени возникает необходимость использовать условные операторы WHERE в SQL Server. Это означает создание хранимой процедуры, которая фильтрует параметры в зависимости от различных критериев. В прошлом были предприняты попытки использовать операторы IF или динамический T-SQL для достижения этой цели, но эти решения оказались неправильными и не масштабируемыми.

Однако существует решение, которое является саргабельным, что означает, что оно может использовать индексы и работать эффективно. Путем использования оператора “1=@MyCondition” в операторе WHERE, где @MyCondition – это переменная, оптимизатор SQL не распознает его как тавтологию и позволяет использовать различные условия WHERE в зависимости от необходимости.

Давайте рассмотрим пример сценария, чтобы лучше понять эту концепцию. Предположим, нам нужно создать список недель университета по номеру недели и номеру дня недели с использованием общего выражения таблицы (CTE). Проблема заключается в том, чтобы создать этот список с возможностью показывать или скрывать праздники. Вот пример кода CTE:

if @MidDate<@pSchoolEndDate begin
  ;with cte ([Date],Wk,DOW)
  as (
  select @StartDate,@Wk,datepart(dw,@StartDate)
  union all
  select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1)
  from cte
  where [Date]<@MidDate
  )
  insert into @Dates
  select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar)
  ,convert(varchar(10),[Date],101)
  ,Wk
  ,DOW
  from cte where 1=1 and datepart(dw,[Date]) not in (1,7) end

В этом примере мы используем CTE для генерации списка недель университета. Переменная @MidDate используется для определения конечной даты недель университета. Переменная @Dates используется для хранения результатов.

Затем нам нужно обработать праздники. Мы можем использовать объединение MERGE JOIN для определения, показывать или скрывать праздники. Вот пример кода MERGE JOIN:

merge @Dates as d
using (select [Date]
        from @Holidays) h on d.[Date]=h.[Date]
when matched and 0=@pShowHolidays then
  delete
when matched and 1=@pShowHolidays then
  update set d.WeekAndDay='Holiday'
when not matched then
  insert([Date])
  values('19000101');

В этом коде мы объединяем таблицу @Dates с таблицей @Holidays на основе даты. Если параметр @pShowHolidays установлен в 0, даты праздников удаляются из таблицы @Dates. Если он установлен в 1, столбец WeekAndDay в таблице @Dates обновляется на ‘Holiday’ для дат праздников. Если дата не найдена ни в одной из таблиц, она вставляется в таблицу @Dates со значением ‘19000101’ по умолчанию.

Теперь давайте сосредоточимся на условном операторе WHERE. В этом примере у нас есть параметр @pUseCondition, который определяет критерии фильтрации. Вот пример условия WHERE:

where (0=@pUseCondition
or (1=@pUseCondition and d1.[WeekDay]='mon')
or (2=@pUseCondition and d1.[WeekDay]='tue')
or (3=@pUseCondition and d1.[WeekDay]='wed')
or (4=@pUseCondition and d1.[WeekDay]='thu')
or (5=@pUseCondition and d1.[WeekDay]='fri')
or (6=@pUseCondition and d1.[WeekDay] in ('mon','wed','fri'))
or (7=@pUseCondition and d1.[WeekDay] in ('tue','thu'))
or (8=@pUseCondition and d.[Date]>=@pEarliestDate)
or (9=@pUseCondition and d1.[WeekDay] in ('mon','fri'))
or (10=@pUseCondition and patindex(@HideDay,d.WeekAndDay)=0)
)

В этом условии у нас есть различные условия в зависимости от значения @pUseCondition. Например, если @pUseCondition установлен в 1, будут возвращены только понедельники. Если он установлен в 6, будут возвращены только понедельники, среды и пятницы. Параметр @pEarliestDate используется для фильтрации дат, которые больше или равны определенной даты. Параметр @HideDay используется для исключения определенных дней недели.

Чтобы использовать этот код, вы можете вызвать хранимую процедуру и передать необходимые параметры. Например:

exec dbo.tp_TestConditional_WHERE_Clause @pSchoolStartDate='20120826'
  ,@pSchoolEndDate='20130531'
  ,@pHolidayList='20121129,20121130,20121225,20130101,20130107'
  ,@pUseCondition=0  -- 0=Нет условий; 1=Пн,2=Вт,3=Ср,4=Чт,5=Пт; 6=Пн,Ср,Пт; 7=Вт,Чт; 8=Наиболее ранняя дата для возврата; 9=Пн,Пт; 10=Исключить @pHideDay
  ,@pShowHolidays=1  -- 0=Скрыть праздники, 1=Показать праздники
  ,@pHideDay=0       -- Использовать номер дня недели (1=Пн,2=Вт,3=Ср,4=Чт,5=Пт) Использовать с @pUseCondition=10
  ,@pEarliestDate='20130228'

Изменяя значение @pUseCondition, вы можете увидеть разные результаты в зависимости от критериев фильтрации.

В заключение, использование условных операторов WHERE в SQL Server может обеспечить гибкость фильтрации данных на основе различных критериев. Путем использования саргабельных условий и динамического SQL можно создать эффективные и масштабируемые решения. Однако перед внедрением этих решений в рабочую среду необходимо тщательно их протестировать и проверить.