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) Make sure sp_Task1 waits until sp_Task2 is completed to move on to the next line of code.
2) How to check that #Task2_Output was successfully created from code within sp_Task1.
Since you don't mention which DBMS, I assume it is SQL Server, since these kinds of situations usually arise there.
For Question 1 :
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.
For Question 2 :
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.
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).
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.