如何将数组传递到SQL Server存储过程

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

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

当您从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。

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

下面的链接可能会帮助你

将集合传递到存储过程中 .

SQL Server 2016(或更新版本)

你可以传入一个带分隔符的列表或JSON，并使用STRING_SPLIT()或OPENJSON()。

STRING_SPLIT():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List varchar(max)
AS
BEGIN
SET NOCOUNT ON;


SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List varchar(max)
AS
BEGIN
SET NOCOUNT ON;


SELECT value FROM OPENJSON(CONCAT('["',
REPLACE(STRING_ESCAPE(@List, 'JSON'),
',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

我在这里写了更多:

• SQL Server中处理未知参数
• SQL Server with OPENJSON中的有序字符串分割

CREATE TYPE dbo.IDList
AS TABLE
(
ID INT
);
GO


CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List AS dbo.IDList READONLY
AS
BEGIN
SET NOCOUNT ON;
  

SELECT ID FROM @List;
END
GO

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();


DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));


// populate DataTable from your List here
foreach(var id in employeeIds)
tvp.Rows.Add(id);


using (conn)
{
SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
// these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
tvparam.SqlDbType = SqlDbType.Structured;
tvparam.TypeName = "dbo.IDList";
// execute query, consume results, etc. here
}

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 PROCEDURE dbo.DoSomethingWithEmployees
@List VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
  

SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ',');
END
GO

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

这是基于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，而不需要创建新的表类型的麻烦，直到我发现这个链接，下面是我如何应用到我的项目:

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

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

希望大家喜欢

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

• 对于分割带分隔符的列表，基于sqlclr的分割器是最快的。如果你想自己编写，有很多例子，或者你可以下载免费的CLR函数SQL #库(这是我写的，但String_Split函数和许多其他函数是完全免费的)。
• 分割基于XML的数组可以是快速的，但是你需要使用基于属性的XML，而不是基于元素的XML(这是这里的答案中显示的唯一类型，尽管@AaronBertrand的XML示例是最好的，因为他的代码使用了text() XML函数。有关使用XML拆分列表的更多信息(即性能分析)，请参阅Phil Factor的使用XML在SQL Server中传递列表作为参数。
• 使用tvp非常棒(假设您使用的是至少SQL Server 2008或更新版本)，因为数据被流到过程中，并显示为预先解析的强类型表变量。然而，在大多数情况下，将所有数据存储在DataTable中意味着在内存中复制数据，因为它是从原始集合复制的。因此，使用DataTable方法传入TVPs并不适用于较大的数据集(即不能很好地扩展)。
• 与简单的带分隔符的int或string列表不同，XML可以处理一维以上的数组，就像tvp一样。但也像DataTable TVP方法一样，XML不能很好地扩展，因为它需要额外考虑XML文档的开销，所以它在内存中增加了两倍多的数据量。

话虽如此，如果你使用的数据很大，或者不是很大，但一直在增长，那么IEnumerable TVP方法是最好的选择，因为它将数据流传输到SQL Server(就像DataTable方法)，但不需要在内存中复制任何集合(不像任何其他方法)。我在这个回答中发布了一个SQL和c#代码的例子:

将字典传递给存储过程T-SQL . sh

根据我的经验，通过从employeeid创建一个带分隔符的表达式，可以为这个问题提供一个巧妙的解决方案。你应该只创建一个像';123;434;365;'这样的字符串表达式，其中123， 434和365是一些雇员id。通过调用下面的过程并将此表达式传递给它，可以获取所需的记录。您可以很容易地将“另一个表”连接到这个查询中。该解决方案适用于所有版本的SQL server。此外，与使用表变量或临时表相比，它是非常快速和优化的解决方案。

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

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

1. 打开查询编辑器

2. 复制粘贴下面的代码，它将创建一个函数，将字符串转换为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
   

3. 创建以下存储过程

    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
   

4. 使用exec sp_DeleteId '1,2,3,12'，这是一个你想删除的Id字符串，

5. 你可以在c#中将数组转换为字符串，并将其作为存储过程参数传递，如下所示。

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

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

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

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

例子:

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