如何将列表传递给存储过程 [英] how to pass list to stored procedure
本文介绍了如何将列表传递给存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想传递List< string>使用c#进入sql 2005中的存储过程。
解决方案
这实际上比听起来要难得多,因为SQL没有列表甚至概念的概念数组。
根据您对列表的确切要求,这可能有所帮助:
在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ] - 它处理将逗号分隔的vlaues字符串处理成临时表,以便在SQL IN子句中使用,这可能会对你有所帮助。
可能会将xml作为字符串传递给
然后
- 创建XML文档的内部表示。
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT,@ XmlDocument
- 使用OPENXML行集提供程序执行SELECT stmt。
SELECT *
FROM OPENXML(@XmlDocumentHandle,'/ ROOT / Customer / Order / OrderDetail',2)
WITH(OrderID int'../@OrderID',
CustomerID varchar(10)'.. / @ CustomerID',
OrderDate datetime'../@OrderDate',
ProdID int' @ ProductID',
数量'@Quantity')
EXEC sp_xml_removedocument @XmlDocumentHandle
正如Griff在SQL Server 2005中所述,这里不容易讨论将它与SQL 2005一起使用
http://www.sommarskog.se/arrays-in-sql -2005.html [ ^
I want to pass List<string> into stored procedure in sql 2005 using c#.
解决方案
That's actually a lot harder than it sounds, because SQL deosn't have a concept of "lists" or even "arrays".
Depending on exactly what you want to do with the list this might help:
Using comma separated value parameter strings in SQL IN clauses[^] - it deals with processing a commas separated string of vlaues into a temporary table for use in an SQL IN clause, which may go some way to helping you.
may be pass xml as string
and then
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
EXEC sp_xml_removedocument @XmlDocumentHandle
As Griff has stated in SQL Server 2005 it isn't easy here is an article that discusses using it with SQL 2005
http://www.sommarskog.se/arrays-in-sql-2005.html[^]
这篇关于如何将列表传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文