How to execute Stored Procedure in Logic App | How to connect to SQL in Logic App

{tocify} $title={Table of Contents}

Introduction

It is very common scenario in integration space, where we have to connect to the database for some CRUD operations. 

So we have two options either write a query for each operation or create a stored procedure and call it.

Both will serve the purpose, but which is better?

Stored procedures have advantage over queries, like

• Firstly it is pre-compiled (Query compiles every time it execute)
• It is reusable
• It is faster
• More secure
• Reduction in Network Traffic (as it executes at server side)
• Any change in stored procedure code does not affect clients, clients automatically get the new version of it

All right, let's build the solution

Create a Stored Procedure

For this demo, I created a simple  Table

CREATE TABLE products (

    product_name nvarchar(50),

    price int

)

And inserted few rows in it

INSERT INTO products (product_name, price)

VALUES

('Desktop Computer',800),

('Laptop',1200),

('Tablet',200),

('Monitor',350),

('Printer',150)

And a simple Stored Procedure which simply returns Product name and

Price from above table

CREATE PROCEDURE GetProducts

AS

BEGIN

SET NOCOUNT ON

 

SELECT P.product_name,P.price  FROM 

products P

END

Create a Logic App to connect to SQL and execute Stored procedure

Create a logic app with Recurrence trigger and then add another step and search for SQL  and select SQL Server

Then select Execute Stored procedure from the actions

Provide required details and click on Create

Testing

Run the logic app, by forcing the trigger (manual Run) or set the recurrence as per convenience . (I set recurrence at 3 min)

As can be seen in above image, stored procedure was executed and result having 5 rows was returned.

Errors Occurred during above implementation

• Logic App not allowed to access the SQL server | Cannot open server 'xx' requested by the login. Client with IP address 'xx' is not allowed to access the server
• The server was not found or was not accessible
• Cannot open server 'xx' requested by the login. Client with IP address 'xx' is not allowed to access the server.

If you have questions or suggestions, feel free to do in comments section below !!!

Do share if you find this helpful .......

 

                       Knowledge Sharing is Caring !!!!!!

 

Learn More about Logic App

• Developing Logic app standard workflow which uses Map locally  and deploying to Azure
• Developing Logic App Standard Workflow Using Visual Studio Code | Create Logic App Standard Workflow Using Visual Studio Code
• Logic App - Xml to Json using Liquid Map | Append in Liquid Map
• How to use Azure Event Grid Custom Topic | Publishing and Subscribing from Azure Event Grid Custom Topic using Logic App
• Using Azure Storage Account Table as Config Store  for Logic Apps | How to read and write from Logic App to Azure Storage Account Table
• Get Logic App Name in Logic App
• Difference between Logic App Consumption and Logic App Standard 
• Getting Started with Logic App Standard | Overview of Logic App Standard | Basics of Logic App Standard
• How to find count of Logic App executions using Azure Portal
• Azure Functions vs Azure Logic App | Difference between Azure Functions and Azure Logic App
• Getting started with Logic App : Liquid Map | Using Liquid template in Logic app
• How to get actual error message of Scope in Logic App | Exception Handling in Logic app
• Interview questions and answers on Logic Apps | Interview questions for azure logic app developers
• How to execute Stored Procedure in Logic App | How to connect to SQL in Logic App
• How to get current date in logic app | How to format date time in Logic App
• BizTalk Developer getting started with Logic App
• Getting Started with Logic Apps - Fundamentals
• Getting Started with Logic Apps - Enterprise Application Integration
• Getting Started with Logic Apps - AS2
• Getting Started with Logic Apps - EDI X12 Fundamentals
• Getting Started with Logic Apps - XML to EDI X12
• Getting Started with Logic Apps - EDI X12 to XML
• Getting Started with Logic Apps - What happened to the Request?
• Inserting Multiple Records In On Prem SQL Using Logic App
• Inserting data in On Premises SQL Database using Logic Apps
• Installing and Configuring On Premises Data Gateway - By adding user to Active Directory
• XML Batching(Aggregation) in Logic App
• Batching(Aggregating) messages in Logic App
• Debatching(Splitting) JSON Message in Logic Apps - ForEach and SplitOn
• Debatching(Splitting) XML Message in Logic Apps - ForEach and SplitOn
• Securing Logic App with Azure Active Directory authentication
• Removing ns0: prefix from xml output from BizTalk/Logic app XSLT map
• Using Managed Identity in Logic Apps for Calling Active Directory Secured Function App
• Logic Apps : Fetching ISA and GS Segment Values From Interchange Envelope and Mapping
• Logic Apps : For Each Inside a For Each - Fetching values from field in an array inside an array