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;