Logic App workflow to retrieve data from SQL using a stored procedure
Using a stored procedure to retrieve data from SQL in a Logic App is a powerful way to manage complex queries and operations. Here’s a step-by-step guide to help you set this up:
- HTTP Request Trigger:
- Start with the When an HTTP request is received trigger to make your Logic App callable via an HTTP endpoint.
- Add SQL Server Action:
- Add the SQL Server – Execute stored procedure action.
- Set up a connection to your SQL Server database if you haven’t done so already.
- Configure Stored Procedure:
- In the Execute stored procedure action, select the stored procedure you want to execute from the dropdown list.
- If your stored procedure requires parameters, you’ll need to provide the values for these parameters. You can use dynamic content from the HTTP request trigger or other actions in your Logic App.
- Initialize Variable:
- Add an Initialize variable action to store the formatted response.
- Choose the variable type as Array.
- For Each Loop:
- Add a For each action to iterate over the rows returned by the SQL query.
- Set the Items property to the body of the SQL action, for example: body(‘SQL’)?[‘ResultSets’][‘Table1’].
- Append to Array Variable:
- Within the For each loop, add an Append to array variable action.
- Append each item in the desired format to the array variable. For example:
{ "EmpId": "@items('For_each')?['EmpId']", "Name": "@items('For_each')?['Name']", "ContactEmail": "@items('For_each')?['Email']" }
- HTTP Response Action:
- After the For each loop, add an HTTP – Response action.
- Set the status code to 200.
- Set the body to the value of the array variable.
Here's how your Logic App should look:
You can download the logic app template from the estudy247 GitHub repository– la-test-sql-03