Как условно отфильтровать на столбце в операторе Where?
Хорошо, энный условный вопрос о столбце:
Я пишу сохраненный 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 строками ГДЕ фильтр.
Поочередно, у меня могло быть 4 копии запроса и выбора среди них в Операторе выбора. Это оставляет код SQL непосредственно исполняемым (и подвергающийся синтаксису hilighting, и т.д.), но за счет повторения больших блоков кода, так как я не могу использовать СЛУЧАЙ на просто операторе Where.
Есть ли какие-либо другие опции? Какие-либо хитрые соединения или логические операции, которые могут быть применены? Или я должен просто преобладать над ним и должностное лицо динамический SQL?
4 ответа
Есть несколько способов сделать это:
Вы можете сделать это с помощью оператора случая.
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
....
Вы можете иметь сложный, где пункт с большим количеством скобок.
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''
Есть еще, но я думаю, что один из них заставит вас двигаться вперед.
-
Вам не нужно копировать весь запрос 4 раза, просто добавьте все возможности пункта where в единственную копию запроса:
select ID, name, description, ...
from myTable
where (@flag = 'foo' and fooFlag = 1) OR (@flag = 'bar' and barFlag = 1) OR ...
Я бы сделал 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 отдельных запроса по очевидным причинам
.У вас может быть параметр для каждого возможного столбца флага, затем проверьте, равен ли параметр нулю или значение в столбце этому параметру. Затем вы передаете 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.
.