How to get multiple result set using executeStoredProcedureForQuery

Question

Hi Community, 
 Trying to replace store proc. plugin with executeStoredProcedureForQuery, But due to limitation it results only 1000 rows in one result set. 
 As per the documentation a!executeStoredProcedureForQuery() Function - Appian 25.3 , 
 I have modified custome.proerties file with below settings but still not sure how I should get 10 result set with 1000 rows in each? 
 
 conf.executeStoredProcedure.limits.maxRowsPerResultSet=1000 
 conf.executeStoredProcedure.limits.maxTotalRows=10000 
 
 Do I need to change store proc to get multiple result set? 
 can we increases more that 10K records using conf.executeStoredProcedure.limits.maxTotalRows configuration? 
 
 Thanks in advance. 
 Bhushan

Shubham Aware · Answer

Multiple result sets: Different queries, not batches of same data Exceed 10K limit: No, maxTotalRows=10000 is usually a hard platform limit. You cannot increase it beyond Appian's maximum. Solution for >10K rows: Implement pagination in your stored procedure (pass page number/offset) and call the function multiple times.

Harsha Sharma · Answer

Given the limitation the workaround is to loop through the store procedure to return all the data. As per Appian recommendation even the 10k max limit, if applied, should be applied cautiously as it can impact performance.
So suggest you to use the limit and offset in sql code of your store procedure and batch the dataset in a loop to fetch entire dataset.