如何检查被调用存储过程的输出表? [英] How to check for output table of called stored procedure?

问题描述

简单的场景.存储过程sp_Task1 调用sp_Task2，而sp_Task2 有一个名为#Task2_Output 的输出临时表.我希望 sp_Task1 接受 #Task2_Output 并用它执行进一步的交易.

Simple scenario. Stored procedure sp_Task1 calls sp_Task2, and sp_Task2 has an output temp table called #Task2_Output. I want sp_Task1 to take #Task2_Output and perform further transactions with it.

我如何让这两件事发生:

How do I get these two things to happen:

1) 确保 sp_Task1 等待 sp_Task2 完成以继续执行下一行代码.

1) Make sure sp_Task1 waits until sp_Task2 is completed to move on to the next line of code.

2) 如何检查 #Task2_Output 是否从 sp_Task1 中的代码成功创建.

2) How to check that #Task2_Output was successfully created from code within sp_Task1.

推荐答案

因为你没有提到哪个 DBMS，我假设它是 SQL Server，因为这些情况通常会出现在那里.

Since you don't mention which DBMS, I assume it is SQL Server, since these kinds of situations usually arise there.

对于问题 1:

在 SQL Server 2005 中，要使调用存储过程可以访问临时表，您可能必须使用 ##Task2_Output(没错，两个哈希)而不是 #Task2_Output，因为带有两个 # 前缀的临时表名称是有点全局临时表可供调用过程使用，但在上次使用后被销毁.但是，如果表名不是动态分配的，您可能需要注意名称冲突.

In SQL Server 2005, for a temporary table to be accessible to the calling stored procedure, you may have to use ##Task2_Output (that's right, two hashes) and not #Task2_Output, since temporary table names with two # prefixes are kinda global temporary tables available to the calling procedure but destroyed after their last use. However, you may have to look out for name name conflicts, if the table names are not dynamically assigned.

对于问题 2:

使用 RETURN 语句获取 sp_Task2 以返回返回代码.如果表创建成功，则返回 1.如果表创建失败(通过在语句后立即检查 @@ERROR 得知)，则返回 -99.

Get sp_Task2 to return a Return Code, with a RETURN statement. If the table creation succeeds, say, Return 1. If the table creation fails (known by checking @@ERROR immediately after the statement), then, say, Return -99.

在 sp_Task1 中，调用 sp_Task2 如下:

In sp_Task1, call sp_Task2 like the following :

Declare @MyRetCode_Recd_In_Task1 int

EXECUTE @MyRetCode_Recd_In_Task1 = sp_Task2 (with calling parameters if any). 

以 ReturnCode = StoredProcName 方式调用存储过程，sp_Task2 返回的返回码由 sp_Task1 接收.然后您可以检查 sp_Task1 中的返回代码以查看是否一切正常.

Calling a stored procedure with a ReturnCode = StoredProcName fashion, the return code returned by the sp_Task2 is received by sp_Task1. You can then check the return code in sp_Task1 to see if all is well.

这篇关于如何检查被调用存储过程的输出表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！