Запрос с заранее неизвестным числом параметров

[Arinyshka]

Идея примерно следующая. Мне понадобилось сделать выборку из таблицы некоторых записей. Пусть пока сам запрос будет на уровне примитивного  только с одним условием

Код:

CREATE PROCEDURE dbo.ctf_sel_CertGoodItem
@id_ttn int
AS
BEGIN
 select
p.id_postavka,
p.seria,
p.date
  from
 Postavka p
 where
 p.id_postavka = @id_ttn
END

Проблема вот в чем: пользователь в дереве приложения галочками отмечает нужные ему накладные. То есть сколько раз мне понадобиться выполнить запрос - кто ж его знает. Результаты его будут обрабатываться другой библиотекой, так что циклом гонять запрос на единичную по onchange статуса выбранности накладной неудобно. Мне бы все результаты одной таблицей...
Вопрос примерно вот в чем: можно ли передать в хранимую массивчик интовых записей. Чтобы для каждого элемента массива вернулся результат...
Пока мне пришла только одна идея - научиться создавать и убивать временные таблицы. Создать таблицу, использовать ее в запросе, убить...
Я в правильном направлении мыслю? Или есть проверенные способы? 
Я опять путанно объясняю... 

[Sla]

а что тебе скажет такая конструкция

Код:

select
p.id_postavka,
p.seria,
p.date
  from
 Postavka p
 where
 p.id_postavka in (список id переданных в запрос)

[PooH]

Не, тут надо смотреть по конкретной задаче.

[Dimka]

В любом из предложенных случаев динамический запрос нужен.

В SQL Server это нужно будет сформировать строчку запроса и выполнить её.

Код: (Text)

declare @sql varchar(8000)
set @sql = 'select * from mytable' -- и добавить сюда нужные where условия
exec(@sql)

Основная проблема - как передать в хранимую процедуру список. Можно строчкой (не более 8000 обычных символов char или 4000 юникодных символов nchar) через запятую, а затем написать внутри хранимой процедуры цикл синтаксического разбора этой строки.

Но если мы работаем с SQL Server 2005, можно пойти и другим путём, поскольку у него есть тип данных xml (неограниченного размера).

Можно в процедуру передать параметр типа xml, затем написать запрос, превращающий этот XML в таблицу нужных ID, а затем этот запрос сделать IN подзапросом основного запроса. В этом случае динамический запрос не нужен.

[Arinyshka]

В любом из предложенных случаев динамический запрос нужен.

В SQL Server это нужно будет сформировать строчку запроса и выполнить её.

Код: (Text)

declare @sql varchar(8000)
set @sql = 'select * from mytable' -- и добавить сюда нужные where условия
exec(@sql)

Основная проблема - как передать в хранимую процедуру список. Можно строчкой (не более 8000 обычных символов char или 4000 юникодных символов nchar) через запятую, а затем написать внутри хранимой процедуры цикл синтаксического разбора этой строки.

Но если мы работаем с SQL Server 2005, можно пойти и другим путём, поскольку у него есть тип данных xml (неограниченного размера).

Можно в процедуру передать параметр типа xml, затем написать запрос, превращающий этот XML в таблицу нужных ID, а затем этот запрос сделать IN подзапросом основного запроса. В этом случае динамический запрос не нужен.

Хех... ничего себе... ушла думать  Мы работаем с SQL Server 2005

[PooH]

Перед тем как уходить, опиши конкретный пример - с таблицами и запросом.

[Arinyshka]

Просто и запрос большой, и таблиц много... Есть вот такой запрос:

Код:

CREATE PROCEDURE dbo.ctf_sel_CertArrivRep
--@begindate nvarchar(10),
--@enddate nvarchar(10)
AS

BEGIN

 select distinct
--*
tg.id_Goods,
tg.Name_Goods GOODNAME,
TGS.Name_SubGroup GROUPGOODNAME,
p.id_postavka,
p.N_ttn NUMBERTTNS,
p.Seria,
p.date_ttn DATETTN, 
gp.kolvo PRESENCEONSTORAGE
 from 
 T_Goods  tg,
 T_SubGroup tgs,
 Postavka p
 inner join Goods_pos gp on gp.id_postavka = p.id_postavka
 left outer JOIN Certificate_Postavka cp on cp.id_postavka = gp.id_postavka 
 left outer JOIN  Certificate_Goods cg ON gp.id_goods = cg.id_Goods 
 left outer JOIN CertificateIncome ci ON ci.id_ctf_postavka = cp.id_ctf_postavka and 
                                         ci.id_ctf_goods = cg.id_ctf_goods
   
 where tg.id_goods = gp.id_goods
   and gp.id_postavka = p.id_postavka
   and tg.id_SubGroup = tgs.id_SubGroup
   and ci.id_ctf_income is null
   and gp.kolvo >0
--   and 
--  p.date_ttn between @begindate and @enddate
order by p.N_ttn
END

Это совсем не очевидно, но в конце концов он выбирает ВСЕ товары, на которые нет сертификатов определенного вида.  Его нужно доделать следующим образом: в приложении, писанном на делфи, у пользователя есть возможность выбирать те товары, сертификат на которые его интересует. Соответственно, в мой запрос придет заранее неизвестное количество записей tg.id_Goods, для которых я должна выдать результат. Вот я и думаю: то ли еще из делфы формировать временную таблицу, которую добавить в запрос по inner join, то ли выполнять по замыслу Dimka, если сумею...

[Arinyshka]

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

[PooH]

На систаксис не обращай внимания, я с SQL Server никогда не общался... но идея думаю понятна:

Код: (SQL)

CREATE PROCEDURE dbo.ctf_sel_CertArrivRep
--@begindate nvarchar(10),
--@enddate nvarchar(10),
@id_Goods_list VARCHAR(7000)
AS
DECLARE @SQL VARCHAR(8000);
SET @SQL = 'select distinct
--*
tg.id_Goods,
tg.Name_Goods GOODNAME,
TGS.Name_SubGroup GROUPGOODNAME,
p.id_postavka,
p.N_ttn NUMBERTTNS,
p.Seria,
p.date_ttn DATETTN,
gp.kolvo PRESENCEONSTORAGE
 from
 T_Goods  tg,
 T_SubGroup tgs,
 Postavka p
 inner join Goods_pos gp on gp.id_postavka = p.id_postavka
 left outer JOIN Certificate_Postavka cp on cp.id_postavka = gp.id_postavka
 left outer JOIN  Certificate_Goods cg ON gp.id_goods = cg.id_Goods
 left outer JOIN CertificateIncome ci ON ci.id_ctf_postavka = cp.id_ctf_postavka and
                                         ci.id_ctf_goods = cg.id_ctf_goods
   
 where tg.id_goods = gp.id_goods
   and gp.id_postavka = p.id_postavka
   and tg.id_SubGroup = tgs.id_SubGroup
   and ci.id_ctf_income is null
   and gp.kolvo >0
   and tg.id_Goods in (' + @id_Goods_list + ') order by p.N_ttn';
EXEC(@SQL);
END;

в @id_Goods_list передаешь строку вида "1, 10, 15, 100"

[Arinyshka]

На систаксис не обращай внимания, я с SQL Server никогда не общался... но идея думаю понятна:
в @id_Goods_list передаешь строку вида "1, 10, 15, 100"

Примерно да, суть ясна. Пасиб

[Dimka]

C XML решение будет выглядеть примерно так:

Код: (Text)

CREATE PROCEDURE dbo.ctf_sel_CertGoodItem
   @id_list xml
AS
   DECLARE @id_list_handler int

   EXEC sp_xml_preparedocument @id_list_handler OUTPUT, @id_list

   SELECT
      p.id_postavka,
      p.seria,
      p.date
   FROM
      Postavka p
   WHERE
      p.id_postavka IN
        (SELECT id
         FROM
           OPENXML(@id_list_handler, '/list/item')
           WITH(
             id int))

   EXEC sp_xml_removedocument @id_list_handler
GO

Вызов этой процедуры простой - нужно XML заявленной структуры передать в параметр как строчку, например:

Код: (Text)

EXEC ctf_sel_CertGoodItem '<list><item id="57"/><item id="58"/></list>'

Путь к "записям", хранящимся в XML, определён как "/list/item" - это означает все записи с именем тэга "item" внутри тэга "list" (названия тэгов могут быть произвольными). Поля записи задаются атрибутами (по умолчанию это так, но у OPENXML есть флаговый параметр, в примере пропущенный, который позволяет переключиться с атрибутно-ориентированного представления в элемент-ориентированное представление XML-документа). Названия атрибутов элемента "item" определяют названия полей, и в секции WITH описываются их типы и задаётся полный перечень полей как для таблицы.

Наличие элемента "list" - скорее дань соглашению, что любое множество обрамляется тэгом, описывающим это множество, нежели необходимость.

Вызов системных процедур sp_xml_preparedocument и sp_xml_removedocument обязателен - они управляют жизнью дескриптора XML-документа, по которому SQL Server его регистрирует внутри себя на время работы. Вообще, это осталось с 2000-й версии - там было всё то же самое, только не было типа данных xml. Возможно, в будущих версиях эти дескрипторы уберут.