Передать массив идентификаторов в хранимую процедуру (С#, MSSQL)

#c_sharp #sql #sql_server

Вопрос в общем то заключается в следующем:
Есть список идентификаторов 

List Id = new List() {1,2,3,4,5}


Есть запрос на SQL

SELECT * FROM table
WHERE table.Id in(1,2,3,4,5)
                  ↑↑↑↑↑↑↑↑↑


Нужно передать этот список, как параметр хранимой процедуры.

Конечно же есть ещё и экстремальные варианты типа превращение списка в строковую
переменную, перечисляя элементы через запятую... 

DECLARE @Id nvarchar(50) = '1,2,3,4,5'
EXEC('SELECT * FROM table
WHERE table.Id in('+@Id+')')


Стоит ли заморачиваться? 
    

Ответы

Ответ 1

Если нужно хранимой процедуре передать список, это можно сделать с помощью xml, хотя
самым оптимальным, на мой взгляд является применение User-defined table type. Для этого
в MSSQL создается табличный тип (который может содержать один или несколько столбцов):

CREATE TYPE [dbo].[intTable] AS TABLE(
    [id] [int]
)


Создадим процедуру:

CREATE PROCEDURE dbo.spProc
    @ids intTable READONLY
AS
BEGIN
    SELECT * FROM table1
    WHERE id IN(SELECT id FROM @ids)
END


При вызове хранимой процедуры из C# в качестве аргумента передается DataTable:

List ids = new List() { 1, 2, 3, 4, 5 };
DataTable data = new DataTable();
data.Columns.Add("id", typeof(int));
foreach(var r in ids)
{
    data.Rows.Add(r);
}
using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "dbo.spProc";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ids", SqlDbType.Structured);
        cmd.Parameters["@ids"].TypeName = "intTable";
        cmd.Parameters["@ids"].Value = data;
        var reader = cmd.ExecuteReader();                    
    }
}


Подобным образом можно передать в хранимую процедуру и более сложную по структуре
таблицу.