首页 / 知识

从存储过程中访问结果集Transact-SQL SQL Server

2023-04-15 02:57:00

Access to Result sets from within Stored procedures Transact-SQL SQL Server

我正在使用SQL Server 2005，我想知道如何从transact-sql中访问不同的结果集。以下存储过程返回两个结果集，如何从另一个存储过程中访问它们？

1
2
3
4
5

CREATE PROCEDURE getOrder (@orderId AS NUMERIC) AS
BEGIN
SELECT order_address, order_number FROM order_table WHERE order_id = @orderId
SELECT item, number_of_items, cost FROM order_line WHERE order_id = @orderId
END

我需要能够分别遍历两个结果集。

编辑：只是为了澄清问题，我想测试存储过程。我有一组从VB.NET客户端使用的存储过程，该存储过程返回多个结果集。这些将不会更改为表值函数，实际上我根本无法更改过程。更改程序不是一种选择。

过程返回的结果集是不同的数据类型或列数。

简短的答案是：您无法做到。

在T-SQL中，如果不像其他人建议的那样更改存储过程，则无法访问嵌套存储过程调用的多个结果。

为了完成此过程，如果该过程返回单个结果，则可以使用以下语法将其插入到临时表或表变量中：

1 2	INSERT INTO #Table (...columns...) EXEC MySproc ...parameters...

您可以对返回多个结果的过程使用相同的语法，但该过程仅处理第一个结果，其余的将被丢弃。

通过创建一个包含内部数据集的SQL2005 CLR存储过程，我很容易做到这一点。

您会看到，默认情况下，新的SqlDataAdapter将.multiple-result-set sproc填充到多表数据集中。这些表中的数据可以依次插入到您要编写的调用过程中的#Temp表中。 dataset.ReadXmlSchema将为您显示每个结果集的架构。

第1步：开始编写sproc，它将从多结果集sproc中读取数据

一种。根据架构为每个结果集创建一个单独的表。

1
2
3
4
5
6
7

CREATE PROCEDURE [dbo].[usp_SF_Read] AS
SET NOCOUNT ON;
CREATE TABLE #Table01 (Document_ID VARCHAR(100)
, Document_status_definition_uid INT
, Document_status_Code VARCHAR(100)
, Attachment_count INT
, PRIMARY KEY (Document_ID));

b。此时，您可能需要声明一个游标以重复调用将在此处创建的CLR proc：

步骤2：制作CLR Sproc

1
2
3
4
5
6

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub usp_SF_ReadSFIntoTables()

END Sub
END Class

一种。使用New SqlConnection("context connection=true")连接。

b。设置命令对象(cmd)以包含多结果集的proc。

C。使用以下命令获取所有数据：

1
2
3
4
5

Dim dataset AS DataSet = NEW DataSet
WITH NEW SqlDataAdapter(cmd)
.Fill(dataset) ' get all the data.
End With
'you can USE dataset.ReadXmlSchema at this point...

d。遍历每个表，并将每一行插入适当的临时表(在上面的第一步中创建)。

最后说明：
以我的经验，您可能希望在表之间建立一些关系，以便知道每个记录来自哪一批。

仅此而已！

?西雅图附近的肖恩

您也可以做一个杂技。向您的存储过程中添加一个可选参数N int。将N的默认值设置为-1。如果N的值为-1，则执行选择的每一项。否则，请执行第N个选择，而仅执行第N个选择。

例如，

1
2
3
4
5

IF (N = -1 OR N = 0)
SELECT ...

IF (N = -1 OR N = 1)
SELECT ...

没有指定N的sproc调用者将获得一个包含多个表的结果集。如果需要从另一个存储过程中提取一个或多个这些表，只需调用存储过程为N指定一个值即可。您必须为要提取的每个表调用该存储过程一次。如果从结果集中需要多个表，效率会很低，但是它确实可以在纯TSQL中工作。

请注意，INSERT INTO ... EXEC语句还有一个额外的，未记录的限制：它不能嵌套。也就是说，EXEC调用的存储过程(或依次调用的任何过程)本身无法执行INSERT INTO ... EXEC。似乎每个进程都有一个暂存器来累积结果，并且如果嵌套了它们，则在调用者打开它时，您将得到一个错误，然后被调用者尝试再次打开它。

Matthieu，您需要为每种"类型"的结果维护单独的临时表。另外，如果您要多次执行同一调用，则可能需要在该结果中添加一个额外的列，以表明它来自哪个调用。

可悲的是，这是不可能的。问题是，当然，没有SQL语法允许它。当然，这是在"幕后"发生的，但是您无法在TSQL中获得这些其他结果，只能从应用程序通过ODBC或其他方式获得。

与大多数事情一样，有一种解决方法。诀窍是在TSQL中使用ole自动化创建一个ADODB对象，该对象依次打开每个结果集并将结果写到您指定的表中(或对结果集执行任何操作)。如果您感到痛苦，也可以在DMO中进行。

有两种方法可以轻松地做到这一点。将结果粘贴到临时表中，然后从存储过程中引用该临时表。另一种选择是将结果放入一个XML变量中，该变量用作OUTPUT变量。

但是，这两种选择都有其优缺点。对于临时表，您需要在创建调用过程的脚本中添加代码，以在修改过程之前创建临时表。同样，您应该在过程结束时清理临时表。

使用XML，可能会占用大量内存并且速度很慢。

您可以将它们选择到临时表中或编写表值函数以返回结果集。是否在询问如何遍历结果集？

结果集存储过程访问过程中