Iterate Over the Results of an Azure SQL Stored Procedure in an Azure Logic App

Introduction

If you need to quickly create a business workflow or other B2B integration, look no further then Azure Logic Apps. Azure Logic Apps is a cloud based workflow and integration service. With the multitude of built in connectors you can easily automate many common business workflows. The Azure SQL Database connector and execute stored procedure action were of interest to me when I needed to implement an automated email notification. The output of a stored procedure from a source system would provide me a list of users who would receive reminder emails. Azure Logic Apps provides the capability of iterating over results of an action, however iterating over the results of a stored procedure is not as straight forward as some of the other examples. In this post I am going to use the automating email notifications example to illustrate what I needed to do to iterate over the results of a stored procedure.

Prerequisites

  • You will need an Azure Subscription. Free Trial
  • Azure SQL DB
  • Stored Procedure with results to iterate over

Azure SQL DB

The first step in completing this workflow is to have an Azure SQL Database with a stored procedure returning data to iterate over. Below I have included a sample stored procedure which returns a list of managers I would like to notify.

CREATE procedure [dbo].[GetManagersToNotify]
as
begin

 CREATE TABLE #tempManagers(
 ID int,
 Name varchar(20), 
 Email varchar(20))


 insert into #tempManagers
 Values
	(1, 'ManagerA', 'ManagerA@contoso.com'),
	(2, 'ManagerB', 'ManagerB@contoso.com'),
	(3, 'ManagerC', 'ManagerC@contoso.com'),
	(4, 'ManagerD', 'ManagerD@contoso.com'),
	(5, 'ManagerE', 'ManagerE@contoso.com')


select *
from #tempManagers

drop table #tempManagers

end

Azure Logic App

Now to setting up the Logic App in the Azure portal. Selecting New -> Web + Mobile -> Logic App brings up the Create Logic App blade. After filling in the appropriate information we will be taken to the Logic App designer with a myriad of prefab templates. In our case we want to build a blank logic app, by selecting the Blank Logic App tile.

Untitled

Every logic app needs to begin with some triggering event. Here we will be choosing the Recurrence. This will allow us to schedule the logic app execution, select the recurrence item and enter your desired execution frequency.

Untitled2

Now that we have defined our triggering event, we need to execute our stored procedure. To do that we select New Step -> Add an action.

Untitled4

This will bring up a similar tile, however it will have a lot more available services to choose from. In the search for more actions box, enter SQL to bring up the available SQL actions to be invoked. We are interested in selecting Execute stored procedure.

Untitled5

If there is a previously created connection to a SQL Database, that connection will be used and we can immediately select the desired stored procedure.

Untitled7

If we need to create a new connection we can click Change Connection which will then list all of the available connections. Then clicking Create New will bring up the new connection form tile.

Untitled6

After we are properly connected to the desired database and the stored procedure is selected, we can add the iteration logic. Just as before to add the next step in the workflow we click New Step. This time however we will click the More option and Add a for each.

Untitled8

This will create a For Each Container Tile, this sets up the scope for the iteration.

Untitled9a

The key piece of information here is how to access the results of the stored procedure from the previous step. Enter the following statement in the For Each Container textbox @body(‘Execute_stored_procedure’)[‘ResultSets’][‘Table1’], this sets up the For Each Container to loop over each item returned from the stored procedure. (Take note of how accessing the items is indexed and if you are returning multiple results from the stored procedure how it may change depending on which result set you need to access.)

Once the iteration is defined the next step is to add an action which will be executed for each result item. After clicking Add an Action we will see the similar actions tile come up to search for the action we would like to execute. Here we will search for Office 365 and select Send an email, this will load the office 365 send email template.

Untitled9

Now that we have the send email tile loaded we want to access the discrete columns from the row item we are iterating over. To do that we use the statement in the form @{item()[‘ColumnName’]}. To send the email notification to the manager from the result set I would populate the To field with @{item()[‘Email’]}.

Conclusion

With other connectors the designer provides great intellisense for the results of an action. When using the stored procedure action and expecting a result set, we no longer had access to the intellisense in the designer. We needed to get a little creative to get at the items we wanted, by adding our own workflow definition statements. The entire Logic App JSON definition is viewable by hitting the View Code option in the designer. I would highly recommend looking at the JSON schema to better understand how the Logic App is defined.