Logic Apps SQL Connector – Working with Stored Procedures

Handling Input Data

Let's start with creating an Employees table with following schema:

Create a new stored procedure AddEmployee - It takes all the parameters of Employees table as input. It checks for existence of the employee before inserting a new record and returns the number of records inserted as return value.

We now move to the Logic Apps Designer on Azure Portal and add a new SQL Stored Procedure action. On selecting a stored procedure, the designer probes its signature and automatically shows all input parameters. Here we add an Add Employee action which takes the employee information as input. Notice that the SQL Connector is able to deal with multiple data types - integer, float, boolean, datetime, string etc.

On execution of this Logic App following output is generated for this action. The Return Code is set to 1 in the response body.

And, if we go back to the SQL Azure database, we can see a new record got inserted for this employee.

Handling Return Data

There are a few different ways in which data can be returned from a stored procedure - return code, result sets and output parameters. Following msdn article explains about them - Return Data from a Stored Procedure.

Return Code

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. Building upon the previous example, we can use the Return Code output from the Add Employee action in next actions like in a Condition action as shown below.

Output Parameters

If we specify OUTPUT keyword for a parameter in the stored procedure definition, the procedure can return the value assigned to the parameter to the caller when the procedure exits.

Let's create a new stored procedure GetManager - It takes employee Id as input and @manager as output parameter. It assigns the employee's manager name to the output parameter.

Inside the Logic App designer, let's add another action for Get Manager stored procedure.

On execution of this Logic App following output is generated for this action. The Output Parameters property contains one entry for manager. If there are more output parameters in the stored procedure, they would also show up under Output Parameters.

The output parameters are available for next steps in the Logic App. In the example below, we have an action Get Employee by Name which takes the manager name obtained from previous action as input.

Result Sets

Result Sets are a collection of tabular records generated through SELECT statements in a stored procedure.

In the below example, we have created a Get Reports procedure - it takes manager as input and returns a result set which consists of all his/her direct reports.

The Result Sets contains one entry corresponding to each SELECT statement present in the stored procedure. These entries follow the naming convention of Table1, Table2, ... etc.

Each table is a collection of records. We can use the ForEach construct to iterate over each record and take appropriate action(s).

The complete Logic App definition used during this session can be downloaded from here.