среда, 22 сентября 2010 г.

Как в хранимую процедуру передать массив



Не так давно у меня возникла необходимость передать в хранимую процедуру массив целочисленных значений. Далее в процедуре мне нужно быть что то сделать с каждым переданным числом. Занявшись поиском решения которое меня устроило бы в сложившейся ситуации я несколько увлекся, что привело к написанию данной статьи.
Итак, вариантов у меня получилось три.
Первый вариант – как говорится, вариант в лоб – передача в хранимку CSV-строки с последующей обработкой. Несмотря на то что это самый очевидный вариант, кода в данном случае придется писать больше чем в любом другом. Причиной этого является не очень гибкий набор T-SQL функций для работы со строками. Производительность такого способа будет не на высоте да и в плане наглядности кода – выгглядит он слегка загруженно. Вот как выглядит фрагмент обработки CSV-строки на языке T-SQL:
--

DECLARE @x VARCHAR(500)

SET @x = '111,222,333'



DECLARE @pos INT

DECLARE @piece VARCHAR(500)

DECLARE @table TABLE(id INT)



IF RIGHT(RTRIM(@x),1) <> ','

SET @x = @x + ','



SET @pos = PATINDEX('%,%' , @x)

WHILE @pos <> 0

BEGIN

SET @piece = left(@x, @pos - 1)



INSERT INTO @table

SELECT CAST(@piece AS INTEGER)



SET @x = STUFF(@x, 1, @pos, '')

SET @pos = PATINDEX('%,%' , @x)

END



SELECT t.id AS id FROM @table t

Другой способ передачи массива чисел, это использование конструкций OPENXML. Проблем в данном решении несколько: во-первых нужно дополнительно затрачивать усилия на генерацию соответствующего XML на вызывающей стороне, а во-вторых: не очень высокая производительность при больших объемах данных. Связано это с тем что в данном случае на сервере выделяются большие объемы памяти для работы с XML. Основным же достоинством данного подхода является относительная простота и тот факт что данные конструкции будут работать на SQL сервере версией ниже 2005.
Пример кода представлен ниже:
--

DECLARE @idoc int

DECLARE @x VARCHAR(400)

SET @x = '<Root><item num=''111''/><item num=''112''/><item num=''113''/></Root>'



EXEC sp_xml_preparedocument @iDoc OUTPUT, @x

SELECT CAST(num AS INTEGER) FROM OPENXML(@iDoc, '/Root/item')

WITH ( num varchar(5) )

EXEC sp_xml_removedocument @iDoc



Последний способ – это использование типа XML который появился в SQL Server 2005. Для данного типа предусмотрены соответствующие методы которые позволяют выполнять базовые операции с типом. Перечень всех методов я приводить не буду, ограничусь лишь примером их использования для решения моей задачи. Как видим последний способ является наиболее лакончиным и максимально эфективным.
--

DECLARE @x XML

SET @x = '<Root><num>111</num><num>112</num><num>113</num></Root>'

SELECT T.c.value('.', 'integer') AS id FROM @x.nodes('/Root/num') AS T(c)




 

Комментариев нет: