How to get multiple result set using executeStoredProcedureForQuery

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.

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.

Hi Shubham Aware Shubham. I'm calling this plugin in a local variable. Now, I need to replace it with the function as it is deprecated. I need this function to behave the same as plugin. i need more data than 1000 Need help with that. I am putting code here so that it will be understandable easily.

a!localVariables(
  local!selectedSite: {},
  local!selectedType: {},
  local!selected: false,
  local!finalData: index(
    executestoredprocedure(
      dataSourceName: "jdbc/SPTAM",
      procedureName: "sptam_sp_site_wise_batch",
      inputs: {
        {
          name: "nto",
          type: "VARCHAR",
          value: if(
            or(ri!esoType = "All", ri!esoType = "NTO"),
            "FALSE",
            ""
          )
        },
        {
          name: "eso",
          type: "VARCHAR",
          value: if(
            or(ri!esoType = "All", ri!esoType = "ESO"),
            "TRUE",
            ""
          )
        },
        {
          name: "notFound",
          type: "VARCHAR",
          value: if(
            or(
              ri!esoType = "All",
              ri!esoType = "NOT FOUND"
            ),
            "NOT FOUND",
            ""
          )
        },
        {
          name: "pharma",
          type: "VARCHAR",
          value: if(
            contains(
              ri!companyId,
              cons!SPTAM_DIVISION_ID_LIST[1]
            ),
            cons!SPTAM_DIVISION_ID_LIST[1],
            ""
          )
        },
        {
          name: "sandoz",
          type: "DATE",
          value: if(
            contains(
              ri!companyId,
              cons!SPTAM_DIVISION_ID_LIST[2]
            ),
            cons!SPTAM_DIVISION_ID_LIST[2],
            ""
          )
        },
        {
          name: "toDate",
          type: "DATE",
          value: ri!toDate
        },
        {
          name: "fromDate",
          type: "DATE",
          value: ri!fromDate
        }
      }
    ),
    "result",
    null
  )

Hi Bhushan, great question, the key thing is that a!executeStoredProcedureForQuery() doesn’t automatically paginate or split one large dataset into multiple result sets; it simply returns whatever result sets your stored procedure produces, up to the limits you’ve configured (e.g., max 1,000 rows per result set and a max total of ~10,000 rows). So to get 10 separate sets of 1,000 rows, your stored procedure really needs to be written to return multiple result sets or support pagination/offset so you can loop calls in Appian and stitch the outputs together. You can raise conf.executeStoredProcedure.limits.maxTotalRows above 10K in custom.properties, but be cautious, Appian’s built-in limits are there for performance reasons and increasing them significantly can impact the system. See a related community discussion here: https://community.appian.com/discussions/f/visit-appian-community-edition/29068/executestoredprocedureforquery