Paging using Azure Logic Apps SQL Connector
    • Dark
      Light
    • PDF

    Paging using Azure Logic Apps SQL Connector

    • Dark
      Light
    • PDF

    Article Summary

    #ServerlessTips - Azure Logic Apps
    Author: Kent Weare Integration MVP

    In one of the previous #ServerlessTips, We have discussed how the underlying Azure connector service will include ‘protections’ to avoid unintended performance issues. This is required to prevent organizations from pulling too much data from a service, too quickly.

    The SQL connector is no different. It does include protections from calling the connector too frequently, but it also has some limits on how much data can be retrieved at a time.

    The current limit is 2048 records in a single call.

    For example, we can create a simple logic app that runs on a schedule that will retrieve all rows from our Customers table. Once the records are returned, we will send the record count to Request bin through an HTTP action.
    image.png

    When we run this logic app, we determine that sure enough, only 2048 records are returned, even though our table contains 6762 records.
    image.png

    The Logic Apps designer does warn us, that if we don’t get all of our expected results that we need to use pagination.

    image.png

    We can then use this information to restrict what page of records we want to return through our Offset statement.
    image.png

    When we configure our logic app, we need to provide the Execute stored procedure action with these values.
    image.png

    There is a problem though. We need to manage these parameters at run time in order to increment the pageNumber parameter.

    To address this requirement, we can use the following pattern where we use variables and an Until loop to continue to loop until we run out of records to process.
    image.png

    When we test our logic app we will discover that our loop ran 7 times which is correct.
    image.png

    Our table includes 6762 records. So the first 6 iterations of our loop return 1000 records each. Then the 7th, and final loop, returns 762.
    image.png

    We examined how we can use pagination to return large datasets that exceed the default thresholds. By including an Until loop, we can dynamically provide our page number which can accommodate a growing table as more records are created.

    Logic Apps-1


    Was this article helpful?