Вопрос по sql-server, stored-procedures, c#, tsql – Как передать массив в хранимую процедуру SQL Server

248

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

Например, у меня есть список сотрудников. Я хочу использовать этот список в качестве таблицы и объединить ее с другой таблицей. Но список сотрудников должен быть передан как параметр из C #.

сэр, надеюсь, эта ссылка поможет вамPassing a list/array to SQL Server SP patrick choi

Ваш Ответ

9   ответов
11

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

  • For splitting a delimited list, a SQLCLR-based splitter is the fastest. There are numerous examples around if you want to write your own, or you can just download the free SQL# library of CLR functions (which I wrote, but the String_Split function, and many others, are completely free).
  • Splitting XML-based arrays can be fast, but you need to use attribute-based XML, not element-based XML (which is the only type shown in the answers here, though @AaronBertrand's XML example is the best as his code is using the text() XML function. For more info (i.e. performance analysis) on using XML to split lists, check out "Using XML to pass lists as parameters in SQL Server" by Phil Factor.
  • Using TVPs is great (assuming you are using at least SQL Server 2008, or newer) as the data is streamed to the proc and shows up pre-parsed and strongly-typed as a table variable. HOWEVER, in most cases, storing all of the data in DataTable means duplicating the data in memory as it is copied from the original collection. Hence using the DataTable method of passing in TVPs does not work well for larger sets of data (i.e. does not scale well).
  • XML, unlike simple delimited lists of Ints or Strings, can handle more than one-dimensional arrays, just like TVPs. But also just like the DataTable TVP method, XML does not scale well as it more than doubles the datasize in memory as it needs to additionally account for the overhead of the XML document.

Учитывая все вышесказанное, ЕСЛИ данные, которые вы используете, велики или еще не очень велики, но постоянно растут, тоIEnumerable Метод TVP - лучший выбор, поскольку он передает данные на SQL Server (например,DataTable метод), НО не требует дублирования коллекции в памяти (в отличие от любых других методов). Я разместил пример кода SQL и C # в этом ответе:

Передача словаря в хранимую процедуру T-SQL

35

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

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
Ницца! Мне очень нравится этот подход, где я фильтрую по ключам int! +1
@ MDV2000 спасибо :) На строковых ключах это также имеет хорошую производительность из-за отсутствия приведения столбцов int к varchar ...
5

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

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

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 

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

понятия не имею, почему это не проголосовано, это - ЧИСТЕЕ решение всех им ...
@zaitsman: ЧИСТЫЙ не означает лучший или наиболее подходящий. Один часто отказывается от гибкости и / или «подходящего» сложность и / или производительность, чтобы получить «чистый» код. Этот ответ здесь "хорошо" но только для небольших наборов данных. Если входящий массив@s CSV, тогда было бы быстрее просто разделить это (то есть INSERT INTO ... SELECT FROM SplitFunction). Преобразование в XML медленнее, чем в CLR, а XML на основе атрибутов намного быстрее. И это простой список, но передача в XML или TVP также может обрабатывать сложные массивы. Не уверен, что получается, избегая простого, одноразовогоCREATE TYPE ... AS TABLE.
5

Open the Query Designer

Copy Paste the Following code as it is,it will create the Function which convert the String to 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

Create the Following stored procedure

 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

Execute this SP Using exec sp_DeleteId '1,2,3,12' this is a string of Id's which you want to delete,

You convert your array to string in C# and pass it as a Stored Procedure parameter

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 ;

Это удалит несколько строк, всего наилучшего

Я использовал эту функцию отдельного разбора запятой, она будет работать для небольшого набора данных, если вы проверите план выполнения, это вызовет проблему с большим набором данных и где вам придется несколько списков CSV в хранимой процедуре
24

Когда вы передаете его из 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();
}
6

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

  1. By using datatable
  2. By using XML.Try converting your collection in an xml format and then pass it as an input to a stored procedure

Ссылка ниже может помочь вам

передача коллекции в хранимую процедуру

15

Edit: быстрый код из моего проекта, чтобы дать вам представление:

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value('.','bigint') data
        FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)

Затем вы можете использовать временную таблицу для соединения со своими таблицами. Мы определили arrayOfUlong как встроенную XML-схему для поддержания целостности данных, но вам не нужно этого делать. Я рекомендую использовать его, поэтому здесь приведен краткий код, позволяющий всегда получать XML с длинными значениями.

IF N,OT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO
Я думал, что это плохая идея использовать переменные таблицы, когда есть много строк? Разве не лучше использовать временную (#table) таблицу вместо этого?
@Ganders: я бы сказал, наоборот.
2

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

Это основано на методе SQL 2005 в ответе Аарона и использовании его функции SplitInts (я только что удалил параметр delim, поскольку я всегда буду использовать запятые). Я использую 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)
Вообще говоря, лучше не присоединяться к табличной переменной, а вместо этого к временной таблице. Табличные переменные, по умолчанию, имеют только одну строку, хотя есть хитрость или две хитрости вокруг этого (см. Превосходную и подробную статью @ AaronBertrand:sqlperformance.com/2014/06/t-sql-queries/…).
386
SQL Server 2008 (or newer)

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

Теперь в вашем коде C #:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}
SQL Server 2005

Если вы используете SQL Server 2005, я бы по-прежнему рекомендовал функцию разделения по XML. Сначала создайте функцию:

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

Теперь ваша хранимая процедура может быть:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

И в вашем коде C # вы просто должны передать список как'1,2,3,12'...

Я считаю, что метод передачи табличных параметров упрощает поддержку решения, которое его использует, и часто имеет повышенную производительность по сравнению с другими реализациями, включая XML и разбиение строк.

Входные данные четко определены (никто не должен догадываться, является ли разделитель запятой или точкой с запятой), и у нас нет зависимостей от других функций обработки, которые не очевидны без проверки кода для хранимой процедуры.

По сравнению с решениями, использующими определяемую пользователем XML-схему вместо UDT, это включает в себя аналогичное количество шагов, но, по моему опыту, это гораздо более простой код для управления, обслуживания и чтения.

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.

@bdwain победил бы цель - вам нужно использовать функцию разбиения, чтобы разбить ее на строки, чтобы поместить в TVP. Разбей это в своем коде приложения.
Как я могу использовать параметр таблицы, если только иметь доступ к строке через запятую
@ th1rdey3 Они неявно необязательны.stackoverflow.com/a/18926590/61305
Это здорово, спасибо. Как передать параметры таблицы в виде строки, а не строить ее в C #? то есть будет ли это похоже на @List = [(1, «Jane») (2, «Smith») (3, «Chris»)]
Мне нравится идея параметров таблицы - никогда не думал об этом - ура. Для чего стоит разделитель, необходимо перейти в вызов функции SplitInts ().

Похожие вопросы