如何将数组传递给SQL Server存储过程？
例如，我有一个员工列表。我想将此列表用作表格，并将其与另一个表格连接。但是，员工列表应该从C＃中作为参数传递。
例如，我有一个员工列表。我想将此列表用作表格，并将其与另一个表格连接。但是，员工列表应该从C＃中作为参数传递。
如何将数组传递到SQL Server存储过程中
362
- Sergey
2
1先生，希望这个链接对您有所帮助： 将列表/数组传递给 SQL Server 存储过程。 - Patrick Guimalan
1它是与参数化SQL IN子句相同的类。 - Lukasz Szozda
13个回答
539
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 2008（或更新版本）
首先，在您的数据库中创建以下两个对象：
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
现在在你的C#代码中：
// 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
}
SQL Server 2005
如果您正在使用 SQL Server 2005，我仍然建议使用分割函数而非 XML。首先，创建一个函数：
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
在您的C#代码中，您只需要将列表作为'1,2,3,12'传递即可...
我发现通过表值参数的方法简化了使用它的解决方案的可维护性，并且通常与其他实现（包括XML和字符串拆分）相比具有更高的性能。
输入被清晰地定义（没有人必须猜测分隔符是逗号还是分号），而且我们没有依赖于其他处理函数，这些函数如果不检查存储过程的代码就不明显。
与涉及用户定义XML模式而不是UDTs的解决方案相比，这涉及类似数量的步骤，但根据我的经验，它是更简单的代码来管理、维护和阅读。
在许多解决方案中，您可能只需要一个或几个这些UDT（用户定义类型），您可以将其重用于许多存储过程。像这个例子一样，常见的要求是传递ID指针列表，函数名称描述了这些ID应该表示的上下文，类型名称应该是通用的。
- Aaron Bertrand
12
3我喜欢表格参数的想法——从未想过。 谢谢。说句实话，分隔符需要传递到SplitInts()函数调用中。 - Drammy
@bdwain会失去意义 - 你需要使用split函数将其分解为行，然后再放入TVP中。在应用程序代码中拆分它。 - Aaron Bertrand
1@AaronBertrand 感谢您的回复，我已经弄清楚了。我必须在括号中使用子查询：
SELECT [colA] FROM [MyTable] WHERE [Id] IN (SELECT [Id] FROM @ListOfIds)。 - JaKXz3它们是隐式可选的。 - Aaron Bertrand
我认为有比XML更简单的方法 - 也许我错过了一些上下文，但是OP在哪里声称他们想使用XML呢？ - O. R. Mapper
显示剩余7条评论
68
基于我的经验，通过从员工ID中创建一个分隔的表达式，有一个棘手而不错的解决方案。您只需创建一个字符串表达式，例如';123;434;365;'，其中123、434和365是一些员工ID。通过调用下面的过程并将此表达式传递给它，您可以获取所需的记录。您还可以轻松地将“另一个表”连接到此查询中。这个解决方案适用于所有版本的SQL服务器。与使用表变量或临时表相比，它是一个非常快速和优化的解决方案。
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
- Hamed Nazaktabar
5
不错！我真的很喜欢这种使用 int 键过滤的方法！+1 - MDV2000
@MDV2000 谢谢 :)
对于字符串键，这也具有良好的性能，因为不需要将 int 列强制转换为 varchar... - Hamed Nazaktabar
我来晚了，但这非常聪明！对我的问题非常有效。 - user441058
这太棒了！我一定会使用它，谢谢。 - Omar Ruder
13这不是一个好的解决方案，因为使用“Like %%”会对表进行扫描。 - Aref Karimi
29
使用表值参数作为您存储过程的参数。
当您从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();
}
- Levi W
19
你需要将它作为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模式来维护数据的完整性，但您不必这样做。我建议您使用它，因此以下是一个快速代码，以确保您始终获得包含longs的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
- Fedor Hajdu
2
1我认为在有许多行的情况下使用表变量是一个不好的主意，使用临时表（#table）会更好一些，因为它可以提高性能。 - ganders
1@ganders: 我会说反过来的。 - abatishchev
18
上下文始终很重要，比如数组的大小和复杂度。对于小到中型列表，这里发布的几个答案都可以，不过需要进行一些澄清：
- 对于分割定界符列表，基于 SQLCLR 的拆分器是最快的。如果你想自己编写代码，有很多示例可供参考，或者你可以下载免费的 SQL# CLR 函数库（由我编写，但 String_Split 函数和许多其他函数完全免费）。
- 拆分基于 XML 的数组可能速度很快，但你需要使用基于属性的 XML，而不是元素为基础的 XML（这里只显示了一种类型，即 @AaronBertrand 的 XML 示例，因为他的代码使用了
text()XML 函数）。有关使用 XML 拆分列表的更多信息（即性能分析），请查看 Phil Factor 的 "Using XML to pass lists as parameters in SQL Server"。 - 使用表值参数非常好（假设你使用的是至少 SQL Server 2008 或更新版本），因为数据会被流式传输到过程中，并以表变量的形式显示预解析和强类型化数据。但是，在大多数情况下，将所有数据存储在
DataTable中意味着在内存中复制数据，因此使用将 TVPs 传递给DataTable方法对于更大的数据集（即不可扩展）效果不佳。 - 与简单的定界符分隔的整数或字符串列表不同，XML 可以处理多维数组，就像 TVP 一样。但正如使用
DataTableTVP 方法一样，XML 不会扩展得很好，因为它需要额外计算 XML 文档的开销，从而使数据大小在内存中增加了一倍以上。
如果你使用的数据量很大，或者数据量目前虽然不是很大，但持续增长，那么 IEnumerable TVP 方法是最佳选择，因为它可以将数据流式传输到 SQL Server（类似于 DataTable 方法），但不需要在内存中复制集合（与其他方法不同）。我在这个回答中发布了 SQL 和 C# 代码的示例：
- Solomon Rutzky
15
正如其他人在上面指出的那样，一种方法是将数组转换为字符串，然后在 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, ';')
将会产生：
值 ----- 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
会给您与上述相同的结果(除了列名为“thenumber”进行排序)。 您可以像使用任何其他表格一样使用表格变量，因此如果需要，可以轻松地将其与数据库中的其他表格连接。
请注意，您的SQL Server安装必须在兼容性级别130或更高级别才能识别STRING_SPLIT()函数。 您可以使用以下查询检查兼容性级别：
SELECT compatibility_level
FROM sys.databases WHERE name = 'yourdatabasename';
int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);
然后将sqlparam作为参数传递给上述存储过程。
- Patrick Chu
7
这会对你有所帮助。 :) 接下来请按以下步骤操作，
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 ); GOCreate 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 GOExecute 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 ;
- Charan Ghate
1
1我使用了逗号分隔解析函数，它可以处理小数据集，但如果您检查其执行计划，它会在大数据集和需要在存储过程中使用多个CSV列表的情况下出现问题。 - Saboor Awan
6
SQL Server不支持数组，但有几种方法可以将集合传递给存储过程。
- 通过使用DataTable。
- 通过使用XML。尝试将您的集合转换为XML格式，然后将其作为输入传递给存储过程。
下面的链接可能会对您有所帮助：
- praveen
6
从 SQL Server 2016 开始，您可以将列表作为 NVARCHAR() 引入并使用 OPENJSON。
DECLARE @EmployeeList nvarchar(500) = '[1,2,15]'
SELECT *
FROM Employees
WHERE ID IN (SELECT VALUE FROM OPENJSON(@EmployeeList ))
- TimM
5
我一直在搜索如何将任何数组传递到SQL Server，而不必费心创建新的Table类型的所有示例和答案，直到我找到了这个链接，下面是我将其应用于我的项目的方式：
--以下代码将获取一个数组作为参数，并将该数组的值插入到另一个表中。
--以下代码将获取一个数组作为参数，并将该数组的值插入到另一个表中。
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
希望您喜欢它
- Adam
1
2@zaitsman：CLEANEST并不意味着最好或最合适。为了获得“干净”的代码，人们经常放弃灵活性和/或“适当”的复杂性和/或性能。这里的答案“还行”，但仅适用于小数据集。如果传入的数组
@s是CSV，则直接拆分它（即INSERT INTO...SELECT FROM SplitFunction）会更快。转换为XML比CLR慢，而基于属性的XML无论如何都要快得多。而且这只是一个简单的列表，通过传递XML或TVP也可以处理复杂的数组。不确定通过避免简单的一次性“CREATE TYPE ... AS TABLE”会获得什么好处。 - Solomon Rutzky网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接