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

byMaheshkumar Tiwari •4:27 PM

0

{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

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

Tags: Azure Logic Apps