Handling Result set from multilple cursors in Stored Procedure
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.
BEGINDECLARESQL_STMT1VARCHAR(100);DECLARESQL_STMT2VARCHAR(100);DECLARESQL_STMT3VARCHAR(100);DECLARESQL_STMT4VARCHAR(100);DECLARECSTMTCURSORWITHRETURNONLYFORSTMT1;DECLARECSTMT1CURSORWITHRETURNONLYFORSTMT2;DECLARECSTMT2CURSORWITHRETURNONLYFORSTMT3;DECLARECSTMT3CURSORWITHRETURNONLYFORSTMT4;SETSQL_STMT1='SELECT * FROM TEMP_DEV_DB_OBJ_DETAILS ORDER BY 1;';SETSQL_STMT2='SELECT * FROM TEMP_DEV_OBJ1_DETAILS ORDER BY 1;';SETSQL_STMT3='SELECT * FROM TEMP_OBJ_DT_DETAILS ORDER BY COLUMNID1 ASC;';SETSQL_STMT4='SELECT * FROM TEMP_OBJ_ID_DETAILS ORDER BY 1;';PREPARESTMT1FROMSQL_STMT1;PREPARESTMT2FROMSQL_STMT2;PREPARESTMT3FROMSQL_STMT3;PREPARESTMT4FROMSQL_STMT4;OPENCSTMT;OPENCSTMT1;OPENCSTMT2;OPENCSTMT3;END;