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

byMaheshkumar Tiwari •
1
{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

SQL connector in Logic App


Then select Execute Stored procedure from the actions

select Execute Stored procedure from the actions

Provide required details and click on Create

SQL API configuration in logic app









Testing


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

Execute Stored Procedure in Logic App

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





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:

1 Comments

If you have any suggestions or questions or want to share something then please drop a comment

  1. AnonymousMarch 14, 2023 at 12:08 AM

    Hey, all seems good but how does the azure setup work on recognising what SP to call? and how does Procedure GetProducts get connected to the logic app?

    ReplyDelete
Previous Post Next Post