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

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

下面的链接可能会帮助你

将集合传递给存储过程

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

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

您需要将其作为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 2016开始，您可以将列表作为NVARCHAR()并使用OPENJSON

DECLARE @EmployeeList nvarchar(500) = '[1,2,15]'


SELECT * 
FROM Employees
WHERE ID IN (SELECT VALUE FROM OPENJSON(@EmployeeList ))

如上所述，一种方法是将数组转换为字符串，然后在SQL Server中拆分字符串。

在SQL Server 2016，有一个内置的方法来分割字符串称为

STRING_SPLIT ()

它返回一组可以插入临时表(或实际表)的行。

DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"
SELECT value FROM STRING_SPLIT(@str, ';')

会产生:

value
-----
  123
  456
  789
  246
   22
   33
   44
   55
   66

如果你想变得更花哨:

DECLARE @tt TABLE (
    thenumber int
)
DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"

INSERT INTO @tt
SELECT value FROM STRING_SPLIT(@str, ';')

SELECT * FROM @tt
ORDER BY thenumber

会得到与上面相同的结果(除了列名是“number”)，但是排序了。您可以像使用其他表一样使用table变量，因此如果您愿意，可以轻松地将它与DB中的其他表连接起来。

请注意，您的SQL Server安装必须在兼容级别130或更高，以便STRING_SPLIT()函数被识别。您可以通过以下查询检查您的兼容性级别:

SELECT compatibility_level
FROM sys.databases WHERE name = 'yourdatabasename';

大多数语言(包括c#)都有一个“join”函数，可以用来从数组中创建字符串。

int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);

然后将sqlparam作为参数传递给上面的存储过程。

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

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