SQL: Передача массива значений в хранимую процедуру

Существуют различные методы для того, чтобы передать в хранимую процедуру массив параметров. Я остановился на следующем варианте (дальнейшее описание верно для MS SQL Server 2008 и выше).

Для начала сериализуем список входных параметров (в качестве примера используем целочисленные значения). С целью упрощения задачи пишем расширение к классу List:

    public static class ListExtensions
    {
        /// Приведение к строковому представлению xml
        public static string ToXmlString(this List list)
        {
            XmlSerializer xs = new XmlSerializer(typeof(List));
            MemoryStream ms = new MemoryStream();
            xs.Serialize(ms, list);

            return Encoding.UTF8.GetString(ms.ToArray());
        }
    }

Теперь готовим массив

List list = new List();
list.Add(100);
list.Add(1000);
list.Add(10000);

string xmlString = list.ToXmlString();

xmlString - это наше входное значение для хранимой процедуры, в данном примере оно будет равно:

<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <int>100</int>
  <int>1000</int>
  <int>10000</int>
</ArrayOfInt>

Теперь сделаем sql-функцию, которая упростит нам жизнь. С помощью неё мы будем преобразовывать xml в таблицу с одним целочисленным столбцом:

CREATE FUNCTION f_GenerateIntTableFromXml
(
 @Xml xml
)
RETURNS @Table TABLE
(
 IntValue nvarchar(max)
)
AS
BEGIN
 INSERT INTO @Table (IntValue) 
 SELECT ParamValues.IntValue.value('.', 'int')
 FROM @Xml.nodes('/*/int') as ParamValues(IntValue)
 RETURN
END

Теперь можно сделать хранимую процедуру, которая будет принимать на вход список целочисленных параметров в xml-представлении (наша переменная xmlString):

CREATE PROCDEURE someProc
(
 @Values xml
)
AS
INSERT INTO tblSomeTable
SELECT i.IntValue, /* Ещё значения */
FROM dbo.f_GenerateIntTableFromXml(@Values) as i