Использование временных таблиц для «передачи» в процедуры массивов

Отсутствие массивов в T-SQL создает проблемы, когда необходимо передать в процедуру массив однотипных данных. Этим проблемам посвящено довольно много статей. Мне наиболее интересным кажется подход, при котором в процедуру передается строка с известной структурой. В листиге 21.6 приведена процедура, которая возвращает ведомость наличия товара с определенным кодом (переменная-аргумент @Kod) на складах, коды которых указаны в строке (переменная-аргумент @SkladList).

Листинг 21.6 Процедура получения ведомости наличия товара на указанных складах

1: CREATE PROCEDURE СкладКолТовTempTable

2: /* Ведомость наличия товара с определенным кодом на указанных складах */

3: @Kod char(12), @SkladList nvarchar(200)

4: AS

5: DECLARE @i int

6: DECLARE @ilen int

7: DECLARE @TempTable TABLE (КодСклада varchar (4) NOT NULL)

8: /* сначала заполним временную таблицу массивом-строкой SkladList */

9: IF LEN(@SkladList) > 0

10: BEGIN

11: SET @ilen = LEN(@SkladList) / 4

12: set @i=1

13: WHILE @ilen > 0

14: BEGIN

15: INSERT INTO @T33TEMP (КодСклада)

16: VALUES (SUBSTRING(@SkladList, @i, 4))

17: set @i=@i+4

18: set @ilen=@ilen-1

19: END

20: END

21: /* использовать в предложении WHERE подзапрос из табличной переменной */

22: SELECT a.НаименСклада as 'Склад', b.КоличТовара as 'Кол-во'

23: FROM Подразделения a, Запасы b

24: WHERE b.КодТовара = @Kod AND a.КодСклада=b.КодСклада

25: AND b. КодСклада IN (SELECT КодСклада FROM @TempTable)

26: ORDER BY a.C_NAMEM

Алгоритм заключается в том, что сначала из строки @ SkladList извлекаются коды складов (длиной четыре символа) и помещаются в табличную переменную @TempTable (строки 11–19). Далее (в строках 22–27) инструкция SELECT использует в предложении WHERE подзапрос из этой табличной переменной.

Вызов процедуры СкладКолТовTempTable из Visual Basic может быть следующим:

GoodCode = " 011133442233" ' код товара, конечно, код нужно выбрать из некоторой таблицы

SladStr = " 011133442233" ' коды 0111, 3344 и 2233

rsADO.Source = " EXEC СкладКолТовTempTable @Kod ='" GoodCode & " ', @SkladList=' " SladStr & " ' "

В рассмотренном случае структура списка-аргумента очень простая: четыре символа для каждого элемента. В следующем листинге используется строка-аргумент, в которой хранятся элементы произвольной длины (не более 20 символов), но каждый из них огранивается круглыми скобками, например: “(A)”, “(DVD)” или “(PC-CD)”. Здесь имеется в виду, что наименование каждого товара начинается с кода группы товаров, заключенного в круглые скобки. Код процедуры листинга 21.7 возвращает таблицу кодов и наименований товаров, группы которых содержатся в строке-аргументе.

Листинг 21.7 Процедура получения списка товаров для указанных групп

1: CREATE PROCEDURE СписокТовДляГрупп

2: /* возвращает список товаров, указанных в строке @GroupList групп.

3: группы конкатенированы без пробелов, поскольку их выделяют скобки */

4: @GroupList AS nvarchar(300)

5: AS

6: DECLARE @lenGroupList int, @i int, @stmp varchar(20), @s varchar(1)

7: DECLARE @KodTable TABLE (CODE varchar (20))

8: /* формирование таблицы используемых групп из @GroupList */

9: SET @i=1

10: SET @stmp = ''

11: SET @lenGroupList = LEN(@GroupList)

12: WHILE @i < @lenGroupList +1

13: BEGIN

14: SET @s = SUBSTRING(@GroupList, @i, 1)

15: SET @stmp = @stmp + @s

16: IF @s=')'

17: BEGIN

18: INSERT INTO @KodTable (CODE) VALUES (@stmp)

19: SET @stmp = ''

20: END

21: SET @i=@i + 1

22: END

23: /* формирование возвращаемого набора */

24: SELECT КодТовара, НаименТовара FROM Товары

25: WHERE SUBSTRING(НаименТовара, 1, PATINDEX('%)%', НаименТовара))

26: IN (SELECT * FROM @KodTable)

27: ORDER BY c_namet

В строке 7 объявляется переменная @ KodTable типа TABLE. В цикле WHILE (строки 12–22) из строки-аргумента @GroupList выделяются лексемы, ограниченные круглыми скобками (коды групп товаров), и посредством инструкции INSERT помещаются в единственное поле таблицы @ KodTable (строка 18). В строках 24–27 помещенные в @ KodTable группы товаров используются в SELECT -запросе для выбора необходимых товаров. Для сравнения начальной части наименования товаров с выбранным списком кодов групп используется функция SUBSTRING в сочетании с PATINDEX.