Handling Result set from multilple cursors in Stored Procedure

Teradata Applications

Handling Result set from multilple cursors in Stored Procedure

Hi,

while working with Stored Procedure, I have noticed the following issue:

Its like I have to display 4 different answer sets which I'm trying to do using 4 different cursors as per below code, it's working good when we have data for all the 4 result sets, but it doesn't seems to work if atleast one of the answer set is empty/null.

Example: if Answer set 1,3,4 has data but Answer set 2 is empty then it returns results only Answer set 1 and remaining Answer sets 2,3,4 is returned with empty result sets(even though 3,4 has data).

My expectation is that it has to return all the 4 Answer sets with proper data if present or empty.

Please kindly provide your inputs for the same.

BEGIN
DECLARE SQL_STMT1 VARCHAR(100); 
DECLARE SQL_STMT2 VARCHAR(100);
DECLARE SQL_STMT3 VARCHAR(100); 
DECLARE SQL_STMT4 VARCHAR(100); 
DECLARE CSTMT CURSOR WITH RETURN ONLY FOR STMT1;
DECLARE CSTMT1 CURSOR WITH RETURN ONLY FOR STMT2;
DECLARE CSTMT2 CURSOR WITH RETURN ONLY  FOR STMT3;
DECLARE CSTMT3 CURSOR WITH RETURN ONLY FOR STMT4;
SET SQL_STMT1 = 'SELECT * FROM TEMP_DEV_DB_OBJ_DETAILS ORDER BY 1;';
SET SQL_STMT2 = 'SELECT * FROM TEMP_DEV_OBJ1_DETAILS ORDER BY 1;';
SET SQL_STMT3 = 'SELECT * FROM TEMP_OBJ_DT_DETAILS ORDER BY COLUMNID1 ASC;';
SET SQL_STMT4 = 'SELECT * FROM TEMP_OBJ_ID_DETAILS ORDER BY 1;';
PREPARE STMT1 FROM SQL_STMT1;
PREPARE STMT2 FROM SQL_STMT2;
PREPARE STMT3 FROM SQL_STMT3;
PREPARE STMT4 FROM SQL_STMT4;
OPEN CSTMT;
OPEN CSTMT1; 
OPEN CSTMT2; 
OPEN CSTMT3;
END;