您需要将其作为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

在sql server中不支持array，但是有几种方法可以将collection传递给存储的proc。

通过使用数据表 通过使用XML。尝试将您的集合转换为xml格式，然后将其作为输入传递给存储过程

下面的链接可能会帮助你

将集合传递给存储过程

我花了很长时间才弄明白，所以如果有人需要的话…

这是基于Aaron回答中的SQL 2005方法，并使用了他的SplitInts函数(我只是删除了delim参数，因为我总是使用逗号)。我正在使用SQL 2008，但我想要一些与类型化数据集(XSD, TableAdapters)一起工作的东西，我知道字符串参数与那些工作。

我试图让他的函数在“where in(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)

从SQL Server 2016开始，你可以简单地使用分割字符串

例子:

WHERE (@LocationId IS NULL OR Id IN (SELECT items from Split_String(@LocationId, ',')))

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

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调用中删除多个行。愿一切都好！

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

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