Передача списка целых чисел в хранимую процедуру SQL Server [duplicate]

Я просматривал все примеры и ответы о том, как передать любой массив на сервер sql без проблем с созданием нового типа таблицы, пока я не нашел это linK , ниже, как я применил это в мой проект:

- Следующий код будет получать Array как параметр и вставить значения этого --array в другую таблицу

Create Procedure Proc1 


@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]


    from @xml.nodes('//root/r') as a(t)
END 

Надеюсь, вам понравится это

Это поможет вам. :) Следуйте следующим шагам:

1. Откройте конструктор запросов
2. Копировать Вставьте следующий код так, как он есть, он создаст функцию, которая преобразует String в Int

   CREATE FUNCTION dbo.SplitInts
   (
      @List      VARCHAR(MAX),
      @Delimiter VARCHAR(255)
   )
   RETURNS TABLE
   AS
     RETURN ( SELECT Item = CONVERT(INT, Item) FROM
         ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
           FROM ( SELECT [XML] = CONVERT(XML, '<i>'
           + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
             ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
         WHERE Item IS NOT NULL
     );
   GO
   

3. Создать следующую хранимую процедуру

    CREATE PROCEDURE dbo.sp_DeleteMultipleId
    @List VARCHAR(MAX)
    AS
    BEGIN
         SET NOCOUNT ON;
         DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
    END
    GO
   

4. Выполнить этот SP Используя exec sp_DeleteId '1,2,3,12', это строка идентификатора, которую вы хотите удалить,
5. Вы преобразуйте свой массив в строку в C # и передайте его как параметр хранимой процедуры

   int[] intarray = { 1, 2, 3, 4, 5 };  
   string[] result = intarray.Select(x=>x.ToString()).ToArray();
   

   SqlCommand command = new SqlCommand();
   command.Connection = connection;
   command.CommandText = "sp_DeleteMultipleId";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;
   

Это приведет к удалению нескольких строк, Все лучшее

Мне потребовалось много времени, чтобы понять это, поэтому на случай, если кому-то это понадобится ...

Это основано на методе SQL 2005 в ответе Аарона и использовании его функции SplitInts (я просто удалил delim param, так как я всегда буду использовать запятые). Я использую SQL 2008, но мне нужно что-то, что работает с типизированными наборами данных (XSD, TableAdapters), и я знаю, что строковые параметры работают с ними.

Я пытался заставить его функцию работать в "где (1,2,3) ", и не повезло прямолинейным способом. Поэтому я сначала создал временную таблицу, а затем сделал внутреннее соединение вместо «где в». Вот мой пример использования, в моем случае я хотел получить список рецептов, которые не содержат определенных ингредиентов:

CREATE PROCEDURE dbo.SOExample1
    (
    @excludeIngredientsString varchar(MAX) = ''
    )
AS
    /* Convert string to table of ints */
    DECLARE @excludeIngredients TABLE (ID int)
    insert into @excludeIngredients
    select ID = Item from dbo.SplitInts(@excludeIngredientsString)

    /* Select recipies that don't contain any ingredients in our excluded table */
   SELECT        r.Name, r.Slug
FROM            Recipes AS r LEFT OUTER JOIN
                         RecipeIngredients as ri inner join
                         @excludeIngredients as ei on ri.IngredientID = ei.ID
                         ON r.ID = ri.RecipeID
WHERE        (ri.RecipeID IS NULL)

Основываясь на моем опыте, создавая разделительное выражение из идентификаторов employeeID, для этой проблемы есть сложное и приятное решение. Вы должны создать только строковое выражение, подобное ';123;434;365;', в котором 123, 434 и 365 - некоторые идентификаторы employeeID. Вызвав приведенную ниже процедуру и передав ей это выражение, вы можете получить нужные записи. Вы легко можете присоединиться к «другой таблице» в этот запрос. Это решение подходит для всех версий SQL-сервера. Кроме того, по сравнению с использованием табличной переменной или таблицы temp это очень быстрое и оптимизированное решение.

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

Используйте хранимый в таблице параметр для вашей хранимой процедуры.

Когда вы передаете его с C #, вы добавите параметр с типом данных SqlDb.Structured.

См. Здесь: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Пример:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

В sql-сервере нет поддержки массива, но есть несколько способов, с помощью которых вы можете передать коллекцию в сохраненный процесс.

1. Используя datatable
2. Используя XML.Try конвертирует вашу коллекцию в формате xml, а затем передает ее как вход в хранимую процедуру

. Ниже ссылка может помочь вам

передать коллекцию к хранимой процедуре

Контекст всегда важен, например size и сложность массива. Для небольших и средних списков несколько ответов, размещенных здесь, просто прекрасны, хотя некоторые разъяснения должны быть сделаны:

• . Для разделения списка с разделителями самый сплиттер на основе SQLCLR является самым быстрым. Существует множество примеров, если вы хотите написать свой собственный или вы можете просто загрузить бесплатную библиотеку SQL # функций CLR (которую я написал, но функция String_Split и многие другие) полностью бесплатны ).
• Разделение массивов на основе XML может быть быстрым, но вам нужно использовать XML на основе атрибутов, а не на основе элементов XML (который является единственным типом, указанным в ответах здесь, хотя XML-пример @ AaronBertrand является лучшим, поскольку его код использует функцию XML text(). Для получения дополнительной информации (например, анализа производительности) при использовании XML для разбиения списков проверьте «Использование XML для передачи списков в качестве параметров в SQL Server » Phil Factor.
• Использование TVP отлично (если вы используете хотя бы SQL Server 2008 или новее) по мере того, как данные передаются в proc и отображаются пре- анализируется и строго типизируется как переменная таблицы. В любом случае сохранение всех данных в DataTable означает дублирование данных в памяти, поскольку они копируются из исходной коллекции. Следовательно, используя DataTable меня что передача в ТВП не работает хорошо для больших наборов данных (т. е. не очень хорошо масштабируется).
• XML, в отличие от простых разделительных списков Ints или Strings, может обрабатывать более чем одномерные массивы, подобно TVP. Но также, как и метод DataTable TVP, XML не масштабируется, так как он более чем удваивает объем данных в памяти, поскольку ему необходимо дополнительно учитывать накладные расходы XML-документа.

При всем том, что, если данные, которые вы используете, являются большими или не очень большими, но постоянно растут, то метод IEnumerable TVP является лучшим выбором, поскольку он передает данные на SQL Server (например, метод DataTable ), НО не требует дублирования коллекции в памяти (в отличие от любого другого метода). В этом ответе я привел пример кода SQL и C #:

Пропустить словарь для хранимой процедуры T-SQL