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

这是基于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)

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 2016开始，你可以简单地使用分割字符串

例子:

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

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

当您从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中不支持array，但是有几种方法可以将collection传递给存储的proc。

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

下面的链接可能会帮助你

将集合传递给存储过程

我一直在搜索所有的例子和答案，如何传递任何数组到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 

希望大家喜欢