Execute stored procedure within Azure Logic App fails with Gateway Timeout

If you could reduce the time of SP by reducing the data payload in the tables under JOIN, then you could use pagination to achieve the successful execution via Logic App.

For example let's say you have a stored procedure like sp_UpdateAColumn which updates columns on tableA based on JOINs with tableB and tableC and tableD

Now this does run but takes more than 2 minutes to finish, because of the large number of rows in tableA.

You can reduce the time on this SP by say creating a new column isUpdated on tableA which is say boolean and by default has value =0

So then if you use

SELECT TOP 100 * FROM tableA WHERE isUpdated =0

instead of whole tableA in the JOIN then you should be able to update the 100 rows in under two minutes.

So if you change your definition of SP from sp_UpdateAColumn tosp_UpdateAColumnSomeRows(pageSize int) then in this SP all you need to do is in the JOINs where you use TableA use(SELECT TOP (SELECT pageSize ) * FROM tableA WHERE isUpdated =0) instead.

Now you need to ensure that this new SP is called enough times to process all records, for this you need to use a do-until loop in logic app ( for total rows in TableA/pazeSize times) and call your SP inside this loop.

Try tweaking with PageSize parameter to find optimal paging size.