Не так давно у меня возникла необходимость передать в хранимую процедуру массив целочисленных значений. Далее в процедуре мне нужно быть что то сделать с каждым переданным числом. Занявшись поиском решения которое меня устроило бы в сложившейся ситуации я несколько увлекся, что привело к написанию данной статьи.
Итак, вариантов у меня получилось три.
Первый вариант – как говорится, вариант в лоб – передача в хранимку 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)
