SSRS: динамическое изменение оператора SQL

У меня есть отчет в SSRS 2005, основанный на запросе, похожем на этот:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
AND col2 LIKE '%XYZ%'

Мне нужно иметь возможность динамически включать часть AND предложения WHERE в запрос в зависимости от того, установил ли пользователь флажок. По сути, это динамический оператор SQL, и в этом проблема. Я пробовал несколько подходов безрезультатно. Это возможно? Поддерживает ли SSRS 2005 динамический SQL? Спасибо!


sql-server tsql reporting-services reportingservices-2005
person λ Jonas Gorauskas    schedule 26.09.2008    source источник

Ответы (7)


arrow_upward
15
arrow_downward

Чарльз почти дал правильный ответ.

Должен быть:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
   AND (@checked = 0 OR col2 LIKE '%XYZ%')

Это классический «шаблон» в SQL для условных предикатов. Если @checked = 0, то будут возвращены все строки, соответствующие остатку предиката (col1 = 'ABC'). SQL Server даже не будет обрабатывать вторую половину файла OR.

Если @checked = 1, то он оценит вторую часть OR и вернет строки, соответствующие col1 = 'ABC' AND col2 LIKE '%XYZ%'

Если у вас есть несколько условных предикатов, их можно связать вместе с помощью этого метода (в то время как методы IF и CASE быстро станут неуправляемыми).

Например:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
    AND (@checked1 = 0 OR col2 LIKE '%XYZ%')
    AND (@checked2 = 0 OR col3 LIKE '%MNO%')

Не используйте динамический SQL, не используйте IF или CASE.

person Brannon    schedule 26.09.2008
comment
Да, я терпеть не могу динамический SQL, невероятно сложный для отладки, обслуживания или обновления. Некоторые незначительные улучшения производительности, если у вас есть большое количество необязательных параметров, но даже с параметрами до 30 я получил хорошую производительность, не прибегая к этому. - person Meff; 09.10.2008
comment
Не говоря уже о том, насколько это опасно, с точки зрения безопасности - person AviD; 23.02.2009
comment
Отличный ответ - person Artemination; 17.01.2020

arrow_upward
3
arrow_downward

Как насчет этого. @checked — ваша битовая переменная.

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
AND (@checked <> 0 and col2 LIKE '%XYZ%')

Изменить: Кроме того, если вы не используете хранимую процедуру, используйте ее.

person Charles Graham    schedule 26.09.2008
comment
Это не работает в случае, когда проверено ложно (0). В этом случае он не вернет никаких совпадений, хотя некоторые из них могут существовать. - person Anthony K; 26.09.2008
comment
Ваш ответ великолепен, но в случае, когда мы не можем выполнить на источнике данных и у нас есть доступ только для чтения к данным, подход к хранимой процедуре не будет работать. - person λ Jonas Gorauskas; 27.09.2008

arrow_upward
1
arrow_downward

Возможно, это сработает для вас:

if @checked = 1
    select * from mytable (nolock) where col = 'ABC'
else
    select * from mytable (nolock) where col = 'ABC' AND colw Like '%XYZ%'

Извините, я редко использую SSRS, но если вы можете получить значение флажка в параметре @checked, это должно сработать.

В качестве альтернативы вы можете использовать оператор CASE WHEN.

person Jason Stevenson    schedule 26.09.2008
comment
В SQL Server это может вызвать перекомпиляцию плана запроса, что может снизить производительность. Если вы используете этот шаблон в своих собственных процедурах, рассмотрите возможность разделения, чтобы у вас была основная процедура, которая выбирает, какую дочернюю процедуру вызывать на основе параметров — одна процедура, один запрос. - person Meff; 09.10.2008

arrow_upward
1
arrow_downward

Это будет работать в SSRS 2000, но используется в крайнем случае.

(плохой) ПСЕВДОКОД

="SELECT * FROM MyTable (NOLOCK)
WHERE col1 = 'ABC'"+
iff(condition,true,"AND col2 LIKE '%XYZ%'","")

Ознакомьтесь с Выполнение "динамических" SQL-запросов. из «Автостопом по службам отчетов SQL Server 2000».

person jason saldo    schedule 26.09.2008

arrow_upward
1
arrow_downward

Один из способов сделать это — сгенерировать запрос SSRS в виде выражения. В конструкторе отчетов BIDS настройте запрос следующим образом:

="SELECT * FROM MyTable WITH (NOLOCK) WHERE col1 = 'ABC'" +
 Iif(Parameters!Checked.Value = true," AND col2 LIKE '%XYZ%'","")
person Community    schedule 26.09.2008

arrow_upward
0
arrow_downward

если вы можете использовать хранимые процедуры, вероятно, это будет проще сделать там. передать в ваших параметрах. Создайте строку SQL на основе ваших условий и выполните EXEC для строки sql, ваш сохраненный процесс вернет нужные вам результаты.

person ScaleOvenStove    schedule 26.09.2008
comment
Ваш ответ великолепен, но в случае, когда мы не можем выполнить на источнике данных и у нас есть доступ только для чтения к данным, подход к хранимой процедуре не будет работать. - person λ Jonas Gorauskas; 27.09.2008

arrow_upward
0
arrow_downward

Вы также можете использовать другой подход и использовать функцию Exec:

DECLARE @CommonSelectText varchar(2000)  
DECLARE @CompleteSelectText varchar(2000)  
SELECT @CommonSelectText = 'SELECT * FROM MyTable (nolock) Where Col = ''ABC'' '  

IF @checked = 1   
    SELECT @CompleteSelectText = @CommonSelectText + ' AND ColW LIKE ''%XYZ%'' '  
ELSE  
    SELECT @CompleteSelectText = @CommonSelectText  

EXEC (@CompleteSelectText)  

GO

Обратите внимание на использование двух апострофов ' для обозначения цитируемого текста.

person Anthony K    schedule 26.09.2008