为存储过程使用表值参数。

当您从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();
}

上下文总是很重要的，比如数组的大小和复杂性。对于中小型榜单来说，这里列出的几个答案还不错，但有一些需要澄清的地方:

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

我一直在搜索所有的例子和答案，如何传递任何数组到sql server，而不需要创建新的表类型的麻烦，直到我发现了这个链接，下面是我如何将它应用到我的项目:

——下面的代码将获取一个数组作为参数，并插入该数组的值 ——数组到另一个表

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 

希望大家喜欢

您需要将其作为XML参数传递。

编辑:快速代码从我的项目给你一个想法:

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模式，以维护数据完整性，但您不必这样做。我建议使用它，这里有一个快速代码，以确保您总是得到一个带long的XML。

IF NOT 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

Based on my experience, by creating a delimited expression from the employeeIDs, there is a tricky and nice solution for this problem. You should only create an string expression like ';123;434;365;' in-which 123, 434 and 365 are some employeeIDs. By calling the below procedure and passing this expression to it, you can fetch your desired records. Easily you can join the "another table" into this query. This solution is suitable in all versions of SQL server. Also, in comparison with using table variable or temp table, it is very faster and optimized solution.

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

这对你有帮助。:)按照下面的步骤，

Open the Query Editor Copy Paste the following code as it is, it will create the Function which converts 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 can convert your array to string in C# and pass it as a Stored Procedure parameter as below, 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 ;

这将在单个存储的proc调用中删除多个行。愿一切都好！