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

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

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


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

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

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

下面的链接可能会帮助你

将集合传递给存储过程

CREATE TYPE dumyTable
AS TABLE
(
  RateCodeId int,
  RateLowerRange int,
  RateHigherRange int,
  RateRangeValue int
);
GO
CREATE PROCEDURE spInsertRateRanges
  @dt AS dumyTable READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT  tblRateCodeRange(RateCodeId,RateLowerRange,RateHigherRange,RateRangeValue) 
  SELECT * 
  FROM @dt 
END

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

如上所述，一种方法是将数组转换为字符串，然后在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作为参数传递给上面的存储过程。