Page 1 of 1

Passing SQL sets again as SQL sets

PostPosted: Sat Apr 20, 2013 5:49 am
by george_vt
I have a stored procedure A that returns 9(fixed) sql sets, currentley invoked by a middleware app. I have created a new stored proc B that calls this stored proc A. The new stored proc too is invoked by a middleware app. I was wondering is there a way i could pass the sql sets returned by A in B as another sql set to the calling app, or somehow pass the address back to the app invoking B for the sql sets returned by A(Not CICS).

Re: Passing SQL sets again as SQL sets

PostPosted: Wed Apr 24, 2013 3:45 am
by pmartyn
You should look at creating a Temporary Table. The table can hold data for you and terminates at the end of the connection thread. Here are more details if you are using a compound statement;

If a DECLARE GLOBAL TEMPORARY TABLE statement is specified within the SQL procedure compound statement (defined by BEGIN and END), the scope of the declared global temporary table is the connection, not just the compound statement, and the table is known outside of the compound statement. The table is not implicitly dropped at the END of the compound statement. A declared global temporary table cannot be defined multiple times by the same name in other compound statements in that session, unless the table has been explicitly dropped