如何将数组传递到SQL Server存储过程?
例如，我有一个员工列表。我想使用这个列表作为一个表，并将它与另一个表连接。但是员工列表应该作为参数从c#传递。
当前回答
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中使用OPENJSON进行有序字符串拆分
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模式而不是udt的解决方案相比，这涉及到类似数量的步骤，但根据我的经验，管理、维护和阅读代码要简单得多。
在许多解决方案中，您可能只需要为许多存储过程重用一个或几个这样的udt(用户定义类型)。与本例一样，常见的要求是传递一个ID指针列表，函数名描述这些ID应该表示的上下文，类型名应该是泛型的。
其他回答
如上所述，一种方法是将数组转换为字符串，然后在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，而不需要创建新的表类型的麻烦，直到我发现了这个链接，下面是我如何将它应用到我的项目:
——下面的代码将获取一个数组作为参数，并插入该数组的值 ——数组到另一个表
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
希望大家喜欢
为存储过程使用表值参数。
当您从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();
}
您需要将其作为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
