Blog

SQL - How to call SQL Server Stored procedures more easily with the help of Dataverse plugins

It's now possible to execute SQL Stored Procedures more directly from Power Apps. This post walks through a demonstration of how to do this.

A benefit of using SQL Server with Power Apps is that we can take advantage of stored procedures to improve the performance of operations, particularly those related to bulk update operations.

Until now, calling stored procedures directly from Power Apps hasn't been possible. The typical way to execute a stored procedure is to build a Power Automate Flow that executes the stored procedure and to call the Flow from the Power App.

With the help of the Dataverse Accelerator and Dataverse plugins, we can now call Stored Procedures more easily from Power Apps. This post walks through how to set this up.

Pre-requisite - Create a SQL Stored Procedure

The first step is to create a SQL Stored Procedure. For the purpose of this post, we'll use the following stored procedure.

CREATE PROCEDURE DeleteOldCustomersAndOrders
    @LastLoginDate DATE
AS
BEGIN

    SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Delete the orders that belong to the customers with old last login date
        DELETE FROM Order
        WHERE CustomerID IN
        (SELECT CustomerID FROM Customer WHERE LastLoginDate < @LastLoginDate);

        -- Delete the customers with old last login date
        DELETE FROM Customer
        WHERE LastLoginDate < @LastLoginDate;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;
    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

This procedure carries out a bulk deletion across 2 related tables (customers and orders) inside a transaction and demonstrates a good use case for a stored procedure.

Download Dataverse Accelerator

The next step is to install Dataverse Accelerator through the AppSource link beneath.

https://appsource.microsoft.com/en-us/product/dynamics-365/microsoftpowercatarch.dataversekit1

The installation process will prompt us to specify the environment where we want to install the Dataverse Accelerator.


Running the Dataverse Accelerator

When the installation succeeds, we can start the Dataverse Accelerator through the App list in the Maker Portal.



Adding a Plugin that calls the SQL Stored Procedure

When the Dataverse Accelerator starts, we see the following welcome screen.

From here, we click the 'New plugin' button in the 'Instant plugin' section.

This opens the following screen where we can name the plugin and provide a description.


The important next step is to expand the 'Advanced options' section and to click the 'Launch the plugins wizard'.

This starts the wizard to create a new plugin from an external service (eg, SQL Server). The first screen of the wizard enables us to create or select an existing SQL Server connection.


From the next screen, we select the 'Execute stored procedure (V2) option.


Next, we can select the stored procedure from the 'procedure name' dropdown, and we can specify the values of any stored procedure parameters. To enable the ability to pass a value from Power Apps, we check the 'Expose as input parameter' checkbox.

From the final screen, we can review the details and create the plugin.

Testing the plugin that calls the Stored Procedure

Once we create the plugin, we can test it from the 'Test and Integrate' section.


If the test succeeds, we receive a 'Success' response as shown beneath.

The 'Integrate' tab is very useful as it provides instructions on how to call the plugin from Power Apps and Power Automate.

How to call the Stored Procedure from Power Apps

At this stage, we're ready to call the Stored Procedure from an app. A pre-requisite is to switch on the 'Enable access to Microsoft Dataverse actions' setting.



The next step is to add the Environment data source to the app


We can then call the Stored Procedure using syntax that looks like this:

Environment.new_deletecustomersandorders_c12uw(
      {LastLoginDate: "2019-01-01"}
)

At this point, we can run our app and execute the stored procedure through the plugin.

Potential issues with data types of parameters

A piece of behaviour to be aware of is that in this example, the plugin wizard creates a parameter of data type string that maps to the date parameter of the stored procedure. This unfortunately means that it's not possible to pass date values using this method. Hopefully, this is something that will be addressed at a future point in time.


Conclusion

It's now possible to call SQL stored procedures more directly from Power Apps by using the Dataverse Accelerator and plugins. This post walked through how to use this technique.

  •   Categories: 
  • sql
Previous
Next
Related posts