从存储过程中调用存储过程并返回记录集 [英] Calling a Stored Proc from within a Stored Proc and returning a recordset

问题描述

我有一个回滚一系列操作的存储过程.我想从另一个 SP 中调用它.

I have a Stored Procedure that rolls-back a series of operations. I want to call this from within another SP.

问题是内部 SP 返回一个记录集，其中包含一个指示成功程度的值.

The problem is that the inner SP returns a record set with a single value that indicates the degree of success.

这种方法运行良好并且在我们的上下文中具有一些优势，但回想起来，我会使用返回值或输出参数以传统方式完成它.

This approach worked well and has some advantages in our context, but in retrospect, I would have done it the conventional way with a Return value or an Output parameter.

我可以总是改变这个 SP 以使用这种方法并修改调用代码，但是 a) 我不想涉足任何不必要的代码，并且 b) 在智力水平，我很想知道可能有什么替代解决方案，如果有的话.

I could always change this SP to use this approach and modify the calling code, but a) I don't want to dabble with any more code than I have to, and b) at an intellectual level, I'm curious to see what alternative solution there may be, if any.

我如何(如果有的话)调用这个 SP 并确定返回的单例记录集的值?

How (if at all) can I call this SP and determine the value of the singleton recordset returned?

谢谢

推荐答案

一个存储过程像其他任何一样返回一个记录集，所以你实际上可以这样做:

A stored procedure returns a record set like any other, so you can actually do this:

插入 MyTable (我的价值)

INSERT INTO MyTable ( MyValue )

EXEC dbo.MyStoredProcedure

EXEC dbo.MyStoredProcedure

EXEC 代替了 SELECT 语句.要获取该值，只需从您插入的表中选择即可.通常，这将是一个临时表.

The EXEC takes the place of a SELECT statement. To get the value, just SELECT from the table you inserted into. Typically, this would be a temp table.

这篇关于从存储过程中调用存储过程并返回记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！