Параметры в запросе с предложением in?

В итоге я использовал глобальную временную таблицу в Firebird, сначала вставив значения параметров, а для получения результатов я использую обычное предложение JOIN вместо WHERE ... IN. Временная таблица зависит от транзакции и очищается при фиксации (ON COMMIT DELETE ROWS).

Кому еще интересно. Я сделал это в Firebird 2.5, используя другую хранимую процедуру, вдохновленную этим постом.

Как разделить строку, разделенную запятыми, внутри хранимой процедуры?

CREATE OR ALTER PROCEDURE SPLIT_STRING (
    ainput varchar(8192))
RETURNS (
    result varchar(255))
AS
DECLARE variable lastpos integer;
DECLARE variable nextpos integer;
DECLARE variable tempstr varchar(8192);
BEGIN
  AINPUT = :AINPUT || ',';
  LASTPOS = 1;
  NEXTPOS = position(',', :AINPUT, LASTPOS);
  WHILE (:NEXTPOS > 1) do
  BEGIN
    TEMPSTR = substring(:AINPUT from :LASTPOS for :NEXTPOS - :LASTPOS);

    RESULT = :TEMPSTR;
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = position(',', :AINPUT, LASTPOS);
    suspend;
  END

END

При прохождении SP следующий список

Список разделенных запятыми = 1,2,3,4

и позвони

SELECT * FROM SPLIT_STRING(:CommaSeperatedList)

результат будет:

RESULT
1
2
3
4

А можно использовать следующим образом:

SELECT * FROM MyTable where MyKeyField in ( SELECT * FROM SPLIT_STRING(:CommaSeperatedList) )

Возможно, вам следует написать это так:

SELECT * FROM MATABLE
WHERE MT_ID IN (:MYPARAM1 , :MYPARAM2)

Я не думаю, что это то, что можно сделать. Есть ли какая-то конкретная причина, по которой вы не хотите создавать запрос самостоятельно?

Я использовал этот метод пару раз, хотя он не использует параметры. Он использует список строк и свойство DelimitedText. Вы создаете IDList и заполняете его своими идентификаторами.

Query.SQL.Add(Format('MT_ID IN (%s)', [IDList.DelimitedText]));

Вам также может быть интересно прочитать следующее:
http://www.sommarskog.se/dynamic_sql.html
и
http://www.sommarskog.se/arrays-in-sql-2005.html

Охватывает динамический sql с предложениями «in» и всеми видами. Очень интересно.

Параметры являются заполнителями для отдельных значений, это означает, что предложение IN, которое принимает список значений, разделенных запятыми, не может использоваться с параметрами.

Подумайте об этом так: где бы я ни размещал значение, я могу использовать параметр.

Итак, в таком предложении: IN (:param)

Я могу привязать переменную к значению, но только к 1 значению, например: IN (4)

Теперь, если вы рассмотрите «выражение значения предложения IN», вы получите строку значений: IN (1, 4, 6) -> это 3 значения с запятыми между ними. Это часть строки SQL, а не часть значения, поэтому ее нельзя связать с параметром.

Очевидно, это не то, что вам нужно, но это единственное, что возможно с параметрами.

Ответ от Юриша — решение в двух случаях из трёх:

• если у вас есть ограниченное количество элементов, которые нужно добавить в предложение in
• или, если вы хотите создать параметры на лету для каждого необходимого элемента (вы не знаете количество элементов во время разработки)

Но если вы хотите иметь произвольное количество элементов, а иногда вообще не иметь элементов, то вы можете сгенерировать оператор SLQ на лету. Использование формата помогает.

SELECT * FROM MATABLE WHERE MT_ID IN (:MYPARAM) вместо использования MYPARAM с : используйте имя параметра.

Например, SELECT * FROM MATABLE WHERE MT_ID IN (SELECT REGEXP_SUBSTR(**MYPARAM,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(MYPARAM, '[^,]+', 1, УРОВЕНЬ) НЕ НУЛЬ))**

МАЙПАРАМ-'368134,181956'

Если вы используете Oracle, вам обязательно следует прочитать запись в блоге Тома Кайта именно на эту тему (ссылка).

Следуя примеру г-на Кайта, вот пример:

SELECT *
  FROM MATABLE
 WHERE MT_ID IN
       (SELECT TRIM(substr(text, instr(text, sep, 1, LEVEL) + 1,
                           instr(text, sep, 1, LEVEL + 1) -
                            instr(text, sep, 1, LEVEL) - 1)) AS token
          FROM (SELECT sep, sep || :myparam || sep AS text
                  FROM (SELECT ',' AS sep
                          FROM dual))
        CONNECT BY LEVEL <= length(text) - length(REPLACE(text, sep, '')) - 1)

Где вы бы привязали :MYPARAM к '368134,181956' в вашем случае.

Вот метод, который я использовал в прошлом, чтобы обойти эту проблему с оператором IN. Он строит список «ИЛИ» на основе количества значений, указанных в параметрах (уникальных). Затем все, что мне нужно было сделать, это добавить параметры в порядке их появления в предоставленном списке значений.

var  
  FilterValues: TStringList;
  i: Integer;
  FilterList: String;
  Values: String;
  FieldName: String;
begin
  Query.SQL.Text := 'SELECT * FROM table WHERE '; // set base sql
  FieldName := 'some_id'; // field to filter on
  Values := '1,4,97'; // list of supplied values in delimited format
  FilterList := '';
  FilterValues := TStringList.Create; // will get the supplied values so we can loop
  try
    FilterValues.CommaText := Values;

    for i := 0 to FilterValues.Count - 1 do
    begin
      if FilterList = '' then
        FilterList := Format('%s=:param%u', [FieldName, i]) // build the filter list
      else
        FilterList := Format('%s OR %s=:param%u', [FilterList, FieldName, i]); // and an OR
    end;
    Query.SQL.Text := Query.SQL.Text + FilterList; // append the OR list to the base sql

    // ShowMessage(FilterList); // see what the list looks like. 
    if Query.ParamCount <> FilterValues.Count then
      raise Exception.Create('Param count and Value count differs.'); // check to make sure the supplied values have parameters built for them

    for i := 0 to FilterValues.Count - 1 do
    begin
      Query.Params[i].Value := FilterValues[i]; // now add the values
    end;

    Query.Open;  
finally
  FilterValues.Free;  
end;

Надеюсь это поможет.

Есть один прием, позволяющий использовать обратное условие SQL LIKE.

Вы передаете список как параметр строки (VARCHAR), например '~12~23~46~567~'

Тогда у вас есть запрос типа where ... :List_Param LIKE ('%~' || CAST( NumField AS VARCHAR(20)) || '~%')