In a previous article Paging using continuation token (microsoft.com), we discussed paging using continuation token to return large list of items. In this article, we will discuss another available feature called pagination that is used to achieve the same result; the difference is that pagination is supported by different set of connectors, and turned on and implemented differently.
We will be using SQL Server - Get rows (V2) action for the purpose of this article; for list of connectors that support pagination and the steps to turn it on, check the following link: Get more items or records with pagination - Azure Logic Apps | Microsoft Docs
Some connector actions behave differently than others when pagination is turned on, some might retrieve the results in multiple action calls, while others will retrieve the same results in one action call, as explained below:
1- Multiple action calls - the action returns the results in pages based on threshold value and other settings; for example, Skip Count and Top Count in SQL Server - Get rows (V2) or Continuation Token and Max Item Count in Azure Cosmo DB - Query documents.
2- One action call - the action can retrieve more results up to the pagination limit, but return those results as a single message when the action finishes; for example, Azure Table Storage - Get entities.
The number of rows returned depends on pagination settings as follows:
1- Pagination is turned off and number of rows exceeds default page size (2,048 for SQL Server - Get rows (V2)), default page size of rows are returned, 2,048 rows in this case.
2- Pagination is turned on and threshold is set to value less than number of rows, number of rows returned are in multiples of default page size. For example, if number of rows is 10,000 and threshold is set to 5,000 then number of rows returned is 6,144 rows (3 pages x 2,048 rows).
3- Pagination is turned on and threshold is set to value greater than number of rows, all rows are returned. For example, if number of rows is 10,000 and threshold is set to 11,000 then number of rows returned is 10,000 rows.
The above might vary based on other settings like Skip Count and Top Count for SQL Server - Get rows (V2).
The following steps show how to use SQL Server - Get rows (V2) with pagination turned on to return the complete list of rows from a SQL Server table:
1- Add Variables - Initialize variable action to initialize a variable of type Integer named skipCount, the initial value is set to Zero. This variable will be holding the number of rows to skip in each action call.
2- Add another Variables - Initialize variable action to initialize a variable of type Integer named topCount, the initial value is set to 1500, for the purpose of this example. This variable will be holding the number of rows to return in each action call.
3- Add another Variables - Initialize variable action to initialize a variable of type Integer named returnCount, the initial value is set to Zero. This variable will be holding the number of rows returned in each action call.
4- Add Control - Until action with condition set to @less(variables('returnCount'), variables('topCount')). This control will loop until the returnCount variable value is less than topCount variable value indicating that the final result is reached and no more items are available to return.
5- Inside the Control - Until action, add SQL Server - Get rows (V2) action with its parameters set as follows:
- Skip Count is the integer value used to indicate the number of rows to skip, set to skipCount variable value.
- Top count is the string token used to indicate the result set to return, set to topCount variable value.
6- Turn on Pagination on SQL Server - Get rows (V2) action, following the steps below:
a. On the top right corner of the action, click on the three dots (...).
b. Select Settings.
c. Turn on Pagination setting.
d. Set Threshold to the number of rows that you want the action to return, set to 5000, for the purpose of this example.
e. Click Done.
7- Add Variables - Increment variable action to increment skipCount with the value of the topCount variable.
8- Add Variables - Set variable action to set returnCount to the number of rows returned by each SQL Server - Get rows (V2) action call. The value is set to length(body('Get_rows_(V2)')?['value']).
Finally, you can add Control - For each action to loop through the returned list of rows in each Control - Until action iteration to process them as required.
Final logic app workflow will look as follows:
For SQL Server connector reference, check the following link: SQL Server - Connectors | Microsoft Docs