Directly Executing SQL Server Stored Procedures from Power Apps: A Guide

Introduction:

SQL Server Stored Procedures have long been essential in data operations, particularly in environments deploying Power Apps. Previously, a direct method to call these procedures from Power Apps was lacking. Now, this article reveals how Dataverse plugins and accelerators have successfully bridged this gap.

Power Apps and SQL Server: The Harmony

Integrating Power Apps with SQL Server supercharges our operations. This synergy gets a further boost when incorporating stored procedures, especially during extensive update operations.

In the past, executing a stored procedure via Power Apps required a roundabout method involving Power Automate Flow. Currently, Dataverse plugins and accelerators have made this process much more straightforward.

Setting the Foundation: Creating the Stored Procedure

Our journey begins with the establishment of a SQL Stored Procedure. For illustration, let’s consider a procedure designed for bulk deletion from two interrelated tables:

CREATE PROCEDURE PurgeOldClientsAndPurchases
    @PriorLoginDate DATE
AS
BEGIN

    SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Deleting orders associated with outdated customer logins
        DELETE FROM OrderTable
        WHERE ClientID IN
        (SELECT ClientID FROM ClientTable WHERE LastLogin < @PriorLoginDate);

        -- Removing outdated customers 
        DELETE FROM ClientTable
        WHERE LastLogin  0
       BEGIN
          ROLLBACK TRANSACTION
       END;
    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;
        

This procedure effectively manages bulk deletions, solidifying the role of stored procedures in database management.

Initiating the Dataverse Accelerator

After preparing your procedure, you should next get the Dataverse Accelerator from AppSource. Once installed, you can access it through the Maker Portal’s App list.

Integrating a Plugin to Summon the Stored Procedure

Launching the Dataverse Accelerator, you’re greeted with a user-friendly interface. Here, select the ‘New plugin’ option within the ‘Instant plugin’ section. Importantly, delve into ‘Advanced options’, which initiates the ‘plugin wizard’. This wizard helps integrate external services like SQL Server. Here, you find the option to ‘Execute stored procedure (V2)’, allowing for the selection and parameter setting of your stored procedure. To conclude, a review ensures the accurate creation of the plugin.

Validating the Plugin’s Functionality

Post-creation, the plugin is up for testing under the ‘Test and Integrate’ section. Success is acknowledged by a respective message. Additionally, the ‘Integrate’ tab furnishes details on invoking the plugin via Power Apps or Power Automate.

Validating the Plugin’s Functionality

After creation, test the plugin under the ‘Test and Integrate’ section. A success message confirms proper functioning. Additionally, the ‘Integrate’ tab provides details on invoking the plugin via Power Apps or Power Automate.

Invoking the Stored Procedure from Power Apps

With everything set, you’re ready to invoke the stored procedure from an app. This involves:

• Activating the ‘Enable access to Microsoft Dataverse actions‘ feature.
• Incorporating the Environment data source into the app.
• Using syntax resembling:

Environment.purge_oldclientsandpurchases_x5yrz(
      {PriorLoginDate: "2019-01-01"}
)
        

However, it’s important to note a quirk. In this case, the plugin wizard creates a string data type parameter, mirroring the stored procedure’s date parameter, which may lead to mismatches. We remain hopeful for its rectification in upcoming releases.

Conclusion:

The alliance of Dataverse plugins and accelerators with Power Apps has revolutionized invoking SQL Server Stored Procedures. This article mapped the process, promising enhanced efficiency in database operations.

Should you encounter any hiccups or seek technical guidance, don’t hesitate to contact us. We’re here to support and optimize your journey.