Sdílet prostřednictvím


Logic Apps SQL Connector – Working with Stored Procedures

Handling Input Data

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

Create Employee table

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.

Add Employee sproc

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.

image

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

image

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

image

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.

image

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.

image

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

image

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.

image

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.

image

image

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.

image

image

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.

image

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

image

image

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