例如,我有一个员工列表。我想将此列表用作表格,并将其与另一个表格连接。但是,员工列表应该从C#中作为参数传递。
您可以传递分隔符列表或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';
我在这里写了更多内容:
首先,在您的数据库中创建以下两个对象:
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,我仍然建议使用分割函数而非 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应该表示的上下文,类型名称应该是通用的。
SELECT [colA] FROM [MyTable] WHERE [Id] IN (SELECT [Id] FROM @ListOfIds)
。 - JaKXz基于我的经验,通过从员工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
使用表值参数作为您存储过程的参数。
当您从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();
}
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
上下文始终很重要,比如数组的大小和复杂度。对于小到中型列表,这里发布的几个答案都可以,不过需要进行一些澄清:
text()
XML 函数)。有关使用 XML 拆分列表的更多信息(即性能分析),请查看 Phil Factor 的 "Using XML to pass lists as parameters in SQL Server"。DataTable
中意味着在内存中复制数据,因此使用将 TVPs 传递给 DataTable
方法对于更大的数据集(即不可扩展)效果不佳。DataTable
TVP 方法一样,XML 不会扩展得很好,因为它需要额外计算 XML 文档的开销,从而使数据大小在内存中增加了一倍以上。如果你使用的数据量很大,或者数据量目前虽然不是很大,但持续增长,那么 IEnumerable
TVP 方法是最佳选择,因为它可以将数据流式传输到 SQL Server(类似于 DataTable
方法),但不需要在内存中复制集合(与其他方法不同)。我在这个回答中发布了 SQL 和 C# 代码的示例:
正如其他人在上面指出的那样,一种方法是将数组转换为字符串,然后在 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';
大多数编程语言(包括 C#)都有一个“join”函数,可以用来从数组创建字符串。int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);
然后将sqlparam
作为参数传递给上述存储过程。
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
);
GO
Create 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
GO
Execute 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 ;
SQL Server不支持数组,但有几种方法可以将集合传递给存储过程。
下面的链接可能会对您有所帮助:
从 SQL Server 2016 开始,您可以将列表作为 NVARCHAR() 引入并使用 OPENJSON。
DECLARE @EmployeeList nvarchar(500) = '[1,2,15]'
SELECT *
FROM Employees
WHERE ID IN (SELECT VALUE FROM OPENJSON(@EmployeeList ))
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
希望您喜欢它
@s
是CSV,则直接拆分它(即INSERT INTO...SELECT FROM SplitFunction)会更快。转换为XML比CLR慢,而基于属性的XML无论如何都要快得多。而且这只是一个简单的列表,通过传递XML或TVP也可以处理复杂的数组。不确定通过避免简单的一次性“CREATE TYPE ... AS TABLE”会获得什么好处。 - Solomon Rutzky