如何将数组传递到SQL Server存储过程?
例如,我有一个员工列表。我想使用这个列表作为一个表,并将它与另一个表连接。但是员工列表应该作为参数从c#传递。
如何将数组传递到SQL Server存储过程?
例如,我有一个员工列表。我想使用这个列表作为一个表,并将它与另一个表连接。但是员工列表应该作为参数从c#传递。
当前回答
您需要将其作为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
其他回答
我花了很长时间才弄明白,所以如果有人需要的话…
这是基于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)
您需要将其作为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,而不需要创建新的表类型的麻烦,直到我发现了这个链接,下面是我如何将它应用到我的项目:
——下面的代码将获取一个数组作为参数,并插入该数组的值 ——数组到另一个表
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
希望大家喜欢
在sql server中不支持array,但是有几种方法可以将collection传递给存储的proc。
通过使用数据表 通过使用XML。尝试将您的集合转换为xml格式,然后将其作为输入传递给存储过程
下面的链接可能会帮助你
将集合传递给存储过程