Многозначный параметр SSRS с использованием хранимой процедуры

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

Здесь сжатая версия того, что у меня есть:

CREATE PROCEDURE [dbo].[uspMyStoredProcedure] 
  (@ReportProductSalesGroupID AS VARCHAR(MAX) 
  ,@ReportProductFamilyID AS VARCHAR(MAX)
  ,@ReportStartDate AS DATETIME
  ,@ReportEndDate AS DATETIME)

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate
AND ProductSalesGroupID IN (@ReportProductSalesGroupID)
AND ProductFamilyID IN (@ReportProductFamilyID)

Когда я пытаюсь просто запустить хранимую процедуру, я возвращаю только значения, если я ввожу только одно значение для @ReportProductSalesGroupID и 1 значение @ReportProductFamilyID. Если я попытаюсь ввести два SalesGroupID и/или 2 ProductFamilyID, это не ошибка, но я ничего не верну.

-- Returns data
EXEC uspMyStoredProcedure 'G23',     'NOF',     '7/1/2009', '7/31/2009'

-- Doesn't return data
EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '7/1/2009', '7/31/2009'

В SSRS я получаю сообщение об ошибке:

Неверный синтаксис рядом с ','

Похоже, что разделитель , включается в строку вместо разделителя

Ответы

Ответ 1

Вам нужно три вещи:

В свойствах набора данных SSRS передайте хранимой процедуре многозначный параметр в виде строки с разделителями-запятыми
```
=Join(Parameters!TerritoryMulti.Value, ",")
```
В Sql Server вам нужна функция табличного значения, которая может разделить строку с запятой на обратную обратно в мини-таблицу (например, см. Здесь). изменение: начиная с SQL Server 2016 вы можете использовать встроенную функцию STRING_SPLIT для этого
В хранимой процедуре есть предложение where, похожее на это:
```
WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))
```
... где ufnSplit - ваша функция расщепления из шага 2.

(Полные шаги и код в моем блогемногозначные параметры SSRS с меньшим количеством ошибок'):

Ответ 2

Предположим, что у вас есть список с несколькими значениями @param1

Создайте еще один внутренний параметр в отчете SSRS под названием @param2 и установите для него значение по умолчанию:

=Join(Parameters!param1.value, 'XXX')

XXX может быть любым разделителем, который вы хотите, ЗА ИСКЛЮЧЕНИЕМ запятой (см. ниже)

Затем вы можете передать @param2 в свой запрос или хранимую процедуру.

Если вы попытаетесь сделать это любым другим способом, это вызовет любую строковую функцию, которая использует запятые для разделения аргументов, для отказа. (например, CHARINDEX, REPLACE).

Например Replace(@param2, ',', 'replacement') не будет работать. Вы получите ошибки, такие как "Заменить функцию требуется 3 аргумента".

Ответ 3

Наконец, я смог получить простое решение этой проблемы. Ниже я выполнил все (3) шаги, которые я выполнил.

Я надеюсь, вам понравится:)

Шаг 1 - Я создал глобальную таблицу темпа с одним столбцом.

CREATE GLOBAL TEMPORARY TABLE TEMP_PARAM_TABLE(
  COL_NAME  VARCHAR2(255 BYTE)
) ON COMMIT PRESERVE ROWS NOCACHE;

Шаг 2 - В процедуре разделения я не использовал какой-либо массив или datatable, я непосредственно загрузил значения split в мою глобальную таблицу temp.

CREATE OR REPLACE PROCEDURE split_param(p_string IN VARCHAR2 ,p_separator IN VARCHAR2
)
IS
   v_string VARCHAR2(4000);
   v_initial_pos NUMBER(9) := 1;
   v_position NUMBER(9) := 1;
BEGIN
   v_string := p_string || p_separator;

   delete from temp_param_policy;

   LOOP
      v_position :=
                  INSTR(v_string, p_separator, v_initial_pos, 1);
      EXIT WHEN(NVL(v_position, 0) = 0);

      INSERT INTO temp_param_table
           VALUES (SUBSTR(v_string, v_initial_pos
                         , v_position - v_initial_pos));

      v_initial_pos := v_position + 1;
   END LOOP;
commit;

END split_param;
/

Шаг 3 - В параметрах набора данных SSRS я использовал

=Join(Parameters!A_COUNTRY.Value, ",")

Шаг 4: В начале вашей хранимой процедуры выполняется процедура

Exec split_param(A_Country, ‘,’);

Шаг 5: В вашей хранимой процедуре sql используйте условие, как показано ниже.

Where country_name in (select * from TEMP_PARAM_TABLE)

Ответ 4

Когда SSRS передает этот параметр, он имеет вид: Param1, Param2, Param3.

В процедуре вам просто нужно поместить идентификаторы вокруг каждого параметра. А также идентификаторы вокруг значения, которое возвращается набором данных. В моем случае я использовал точки с запятой.

CREATE OR REPLACE PROCEDURE user.parameter_name ( i_multivalue_parameter ) AS l_multivalue_parameter varchar2(25555) := ';' || replace(i_multivalue_parameter,',',';') || ';'; BEGIN select something from dual where ( instr(l_multivalue_parameter, ';' || database_value_that_is_singular || ';') > 0 ) END;

i_multivalue_parameter передается через SSRS.

l_multivalue_parameter считывает параметр, передаваемый через SSRS, и помещает идентификаторы вокруг каждого значения.

database_value_that_is_singular - это значение, возвращаемое для каждой записи.

Итак, если "Type1, Type2, Type3" передается через SSRS:

i_multivalue_parameter: Type1, Type2, Type3

l_multivalue_parameter: Тип: Type2; Тип3;

database_value_that_is_singular является:; Type1; или; Type2; или; Type3;

Instr вернет значение над 0, если параметр соответствует.

Это работает, даже если все параметры схожи. EG: "Тип A" и "Тип AA". То есть "Тип A" не будет соответствовать "Тип AA".

Ответ 5

Я нашел простой способ для моего решения. Определите значение параметра в отчете как выражение, подобное этому

="'" + Join(Parameters!parm.Value,"','") + "'"

(если вы не можете прочитать его, первый и последний литералы - двойные кавычки, одинарные кавычки, двойные кавычки. Объединяемый литерал - двойные кавычки, одинарные кавычки, запятая, одинарные кавычки, двойные кавычки)

Затем в хранимой процедуре вы можете использовать динамический SQL для создания вашего заявления. Я сделал это, чтобы создать временную таблицу значений, к которой нужно присоединиться в более позднем запросе, например:

CREATE #nametable (name nvarchar(64))

SET @sql = N'SELECT Name from realtable where name in (' + @namelist + ')'

INSERT INTO #nametable exec sp_executesql @sql

@namelist будет именем параметра хранимой процедуры.