Как условно фильтровать столбец в предложении WHERE?

ОК, вопрос об условном столбце umpteenth:

Я пишу хранимый proc, который принимает входной параметр, который сопоставляется с одним из нескольких столбцов флага. Каков наилучший способ фильтрации в запрошенном столбце? Я сейчас на SQL2000, но собираюсь перейти на SQL2008, поэтому я возьму современное решение, если доступно.

Таблица, запрошенная в sproc, выглядит как

ID ...  fooFlag  barFlag  bazFlag  quuxFlag
--      -------  -------  -------  --------
01         1        0       0          1
02         0        1       0          0
03         0        0       1          1
04         1        0       0          0

и я хочу сделать что-то вроде

select ID, name, description, ...
from myTable
where (colname like @flag + 'Flag') = 1

поэтому, если я вызову sproc, как exec uspMyProc @flag = 'foo', я вернусь к строкам 1 и 4.

Я знаю, что я не могу выполнить роль в parens непосредственно в SQL. Чтобы сделать динамический SQL, мне придется заполнить весь запрос в строку, объединить параметр @flag в предложении WHERE и затем выполнить строку. Помимо грязного чувства, которое я получаю при выполнении динамического SQL, мой запрос довольно велик (я выбираю пару десятков полей, присоединяюсь к 5 таблицам, вызывая пару функций), поэтому это большая гигантская строка из-за одной строки в 3-строчном WHERE-фильтре.

В качестве альтернативы я мог бы иметь 4 копии запроса и выбирать среди них в операторе CASE. Это оставляет код SQL непосредственно исполняемым (и подвержен синтаксису hilighting и т.д.), Но за счет повторения больших кусков кода, поскольку я не могу использовать CASE только в предложении WHERE.

Есть ли другие варианты? Какие-нибудь сложные комбинации или логические операции, которые могут быть применены? Или я должен просто преодолеть это и выполнить динамический SQL?

Ответ 1

Есть несколько способов сделать это:

Вы можете сделать это с помощью оператора case.

select ID, name, description, ...
from myTable
where CASE
    WHEN @flag = 'foo' then fooFlag
    WHEN @flag = 'bar' then barFlag
END = 1

Вы можете использовать IF.

IF (@flag = 'foo') BEGIN
    select ID, name, description, ...
    from myTable
    where fooFlag = 1
END ELSE IF (@flag = 'bar') BEGIN
    select ID, name, description, ...
    from myTable
    where barFlag = 1
END

....

У вас может быть сложное предложение where с большим количеством круглых скобок.

select ID, name, description, ...
from myTable
where (@flag = 'foo' and fooFlag = 1)
OR (@flag = 'bar' and barFlag = 1) OR ...

Вы можете сделать это с помощью динамического sql:

DECLARE @SQL nvarchar(4000)

SELECT @SQL = N'select ID, name, description, ...
from myTable
where (colname like ''' + @flag + 'Flag'') = 1'

EXECUTE sp_ExecuteSQL @SQL, N''

Есть больше, но я думаю, что один из них заставит вас идти.

Ответ 2

"В качестве альтернативы я мог бы иметь 4 копии запроса и выбирать среди них в операторе CASE".

Вам не нужно копировать весь запрос 4 раза, просто добавьте все возможности в предложения where в вашей единственной копии запроса:

select ID, name, description, ...
from myTable
where (@flag = 'foo' and fooFlag = 1) OR (@flag = 'bar' and barFlag = 1) OR ...

Ответ 3

Что бы я сделал, это CASE некоторые переменные в начале. Пример:

DECLARE
    @fooFlag int,
    @barFlag int,
    @bazFlag int,
    @quuxFlag int

SET @fooFlag = CASE WHEN @flag = 'foo' THEN 1 ELSE NULL END
SET @barFlag = CASE WHEN @flag = 'bar' THEN 1 ELSE NULL END
SET @bazFlag = CASE WHEN @flag = 'baz' THEN 1 ELSE NULL END
SET @quuxFlag = CASE WHEN @flag = 'quux' THEN 1 ELSE NULL END

SELECT ID, name, description, ...
FROM myTable
WHERE (fooFlag >= ISNULL(@fooFlag, 0) AND fooFlag <= ISNULL(@fooFlag, 1))
AND (barFlag >= ISNULL(@barFlag, 0) AND barFlag <= ISNULL(@barFlag, 1))
AND (bazFlag >= ISNULL(@bazFlag, 0) AND bazFlag <= ISNULL(@bazFlag, 1))
AND (quuxFlag >= ISNULL(@quuxFlag, 0) AND quuxFlag <= ISNULL(@quuxFlag, 1))

Хорошо, что этот запрос состоит в том, что, поскольку возможные значения для "флагов" ограничены, вы можете рассчитать все свои условные условия в качестве предпосылок, а не обертывать в них столбцы. Это гарантирует высокопроизводительный поиск индекса в зависимости от того, какие столбцы индексированы и не требует записи динамического SQL. И это лучше, чем писать 4 отдельных запроса по очевидным причинам.

Ответ 4

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

select id, name, description, ...
from myTable
where (@fooFlag is null or fooFlag = @fooFlag) AND
      (@barFlag is null or barFlag = @barFlag) AND
      ...

Правда, это похоже на идеальный кандидат на создание динамического запроса LINQ и пропущение SPROC после того, как вы перейдете на SQL2008.

Ответ 5

where
   case when @value<>0 then Field else 1 end
   =
   case when @value<>0 then @value else 1 end

Ответ 6

declare @CompanyID as varchar(10) = ''    -- or anyother value

select *  from EmployeeChatTbl chat

  where chat.ConversationDetails like '%'[email protected]+'%' 

                and
                (
                    (0 = CASE WHEN (@CompanyID = '' ) THEN 0 ELSE 1 END) 
                                or 
                    (chat.CompanyID = @CompanyID)
                )

работает

когда присутствует идентификатор компании, тогда фильтрация на его основе выполняется, другой - фильтрация пропускается.