将整个数据集传递给MSSQL 2005中的存储过程

How do I pass a dataset object to a stored procedure? The dataset comprises multiple tables and I'll need to be able to access them from within the SQL.

如何将数据集对象传递给存储过程?数据集包含多个表，我需要能够从SQL中访问它们。

3 个解决方案

#1

It looks like you can do this with SQL Server 2008 or newer (at least with a DataTable). Here are the links:

看起来您可以使用SQL Server 2008或更新(至少可以使用DataTable)来实现这一点。这是链接:

http://www.eggheadcafe.com/community/aspnet/10/10138579/passing-dataset-to-stored-procedure.aspx

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

http://www.sqlteam.com/article/sql -服务器- 2008表-价值-参数

#2

You can use Table valued parameter for passing single table in SQL 2008 http://msdn.microsoft.com/en-us/library/bb675163.aspx

可以使用表值参数在SQL 2008 http://msdn.microsoft.com/en-us/library/bb675163.aspx中传递单个表

或

refer to this article and use SQL CLR procedure to pass dataset http://blogs.msdn.com/b/jpapiez/archive/2005/09/26/474059.aspx

参考本文并使用SQL CLR过程传递数据集http://blogs.msdn.com/b/jpapiez/archive/2005/09/26/474059.aspx

#3

As the article from MusiGenesis' answer states

正如《音乐起源》的文章所言

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

在SQL Server 2005和更早的版本中，不可能将表变量作为参数传递给存储过程。当多行数据需要向SQL Server发送多行数据时，开发人员要么一次发送一行数据，要么提出其他解决方案以满足需求。而VB。Net developer最近通知我，.Net中有一个SQLBulkCopy对象可以同时向SQL Server发送多行数据，数据仍然不能传递给存储的proc。

At the risk of stating obvious here are two more approaches

这里有两种明显的方法

Parametrize your processing procedure

用参数表示你的加工过程

You might re-evaluate if you truly and really need to pass a general table variable. While sometimes this can not be avoided the reason why this is a later addition to the set of features that MS SQL Server has is partially because usually you can get around it by structuring your stored procedures and the flow of your data processing.

您可能会重新评估是否真的需要传递一个通用表变量。虽然有时候这是不可避免的，但是这是MS SQL Server的一组新特性的部分原因，因为通常您可以通过结构化存储过程和数据处理流来绕过它。

If you are able to 'parametrize' your process then you should be able to let stored procedures retrieve full dataset based on a limited number of parameters.

如果您能够“参数化”您的流程，那么您应该能够让存储过程基于有限的参数检索完整的数据集。

This will make the process less flexible, but it will also make it more controlled, which is not a bad thing (similarly like the database which interfaces with applications only on the level of stored procedures is more robust, this approach also, by limiting the flexibility reduces the number of possible cases and consequently the number of possibly unhandeled cases. read: security holes and general bugs)

这将使过程更灵活,但它也将让它更多的控制,这并不是一件坏事(类似像数据库接口与应用程序只在水平更健壮的存储过程,这种方法,通过限制了灵活性降低了可能的病例数,因此可能unhandeled病例的数量。阅读:安全漏洞和一般bug)

Temp tables

临时表

Besides the above there's always approach with temp tables, which can be more or less complicated, depending on the scope of sharing that you need on the data (sharing can be between db users, app users, connections, processes, etc..).

除了上面提到的，还有一种使用temp表的方法，根据数据上需要的共享范围(可以在db用户、应用程序用户、连接、进程之间进行共享)，这种方法或多或少会比较复杂。

Nice side effect is that such approach would allow persistence of the process (which bring you closer to having undo, redo and ability to continue interrupted work).

好的副作用是，这种方法将允许进程的持久性(使您更接近具有撤销、重做和继续中断工作的能力)。

#1