Running Stored Procedures directly with PowerFX

[markinwaukesha]

PowerApps and Stored Procedures

Hopefully this helps someone out there looking to expand or add this capability into their PowerApp, and I hope you have as

much as I did.   If you haven't tried Stored Procedures with PowerApps give it a try.

To begin my example, I created two stored procedures - one that takes a parameter and another that returns a list.   

Some additional items to add onto what I have is additional parameters such as do a lookup using parameter or just give me

all items, as well as try/Catch and Error handling – but this is just for practice focusing on Stored Procedures.

Note: Make sure the user you’re using for the database connection has the ability to execute the stored procedure

sometimes if you're required to work through a DBA have limited permissions.  You’ll know right away because if

you look in the Monitor you’ll see a permission error.

Here are my two stored Procedures.   

Note: I created these in an application I use just for "Proofs of Concept".  The screen and data I show are just to practicing with stored procedures and power apps – in a real production environment you’d want to harden them with additional error checking,

logging and log levels and other items that meet your environment criteria.

Lookup with a parameter procedure

ALTER PROCEDURE [dbo].[uspPowerAppsTestProcedureOnly]
                      @userID int    -- my parameter
AS
BEGIN
    SELECT [UserId]
          ,[UserLogin]
          ,[Active]
          ,[createDtm]
          ,[updateDtm]
          ,[LastCheck]
    FROM [Messages].[Users]
    Where UserId = @userID    -- lookup using parameter
END

LookUp/List with no parameters:

ALTER PROCEDURE [dbo].[uspPowerAppsTestProcedureOnlyForGallery]
AS
BEGIN
    SELECT [UserId]
          ,[UserLogin]
          ,[Active]
          ,[createDtm]
          ,[updateDtm]
          ,[LastCheck]
    FROM [Messages].[Users];
    RETURN;
END

Update your PowerApp Settings to enable the preview feature:

Enable the feature:

There were many articles on this, but I’ll quickly review - since calling stored procedures using PowerFX directly

is a preview feature so you’ll need to go into your app Settings and enable the preview feature.

Add new connections:

To add your stored procedures, it’s similar to adding a table except there will be a new tab called “Stored procedures”.

You’ll still: 

* Choose your data connection – example: SQL Server

* Provide the server, database and credentials.   

Once you hit next, you’ll see multiple tabs:

* Tables/View

* Stored Procedures

Select “stored procedures” and then select the ones you want to use.  

I selected both since I only had two.  Once you add this to your project they will just look like a connection

and will not indicate any stored procedures.

On the screen you select the stored Procedure on, there will be an additional option “safe to use on Galleries and Tables",

you'll want that enabled if you're using the stored procedure to return data.

Note: you’ll never have an option to change that again, so set it how you want to, otherwise you’ll need to remove it

and re-add it again to have a way to change it.

Below is an example, showing just the list of connections but nothing unique about them.

Executing/Running the stored procedure:

To run the stored procedure, you’ll reference it this way:

[Data Connection Name].[Procedure name with schema prefixed onto it]

example using my app, you'll see the "Dbo" schema is just prefixed automatically by PowerApps to the Stored Procedure name.

CityGarage.dbouspPowerAppsTestProcedureOnly

                  |

             Connection Name "dot" Stored Procedure with schema prefixed

If you have parameters then you’ll need to provide those – this works very similar to patching:

Example:  

CityGarage.dbouspPowerAppsTestProcedureOnly({userId:123})

If you’re using a parameter variable – just substitute 123 with your variable

When you call a stored procedure there will be three return elements:

• ResultSets
• ReturnCode
• OutputParameters

I haven’t done anything (yet) with ReturnCode or OutputParameters but for production I would want to include

that check and start using ReturnCode to validate the result.

ResultSets will be what you want to use for data.

In your stored procedure you do not need to provide input or output type declarations (my example was using SQL Server)

you'll just do a Select statement and that result will go into the Tables that are put into the “ResultSets”

Example:

Three Select statements = you’ll have:  Table1, Table2, Table3

One Select = it will go into Table1 

While building my App, I was very generous about having multiple lookups of various kinds, and in my observation,

you can have lookups/Stored Procedures pretty much anywhere you want there wasn’t any limitation I saw yet.

Here are the ones I used,

Note: I needed to obfuscate some of the data

On the button above I do a Lookup with a direct value

On Select action is:

ClearCollect(ColLookUpResults, Table(CityGarage.dbouspPowerAppsTestProcedureOnly({userID:8}).ResultSets.Table1));

You’ll need to use “Table” otherwise the result will go into a single variable and not be very usable from what I encountered

I put the result of my “Get with Param” into a gallery in that example.

My Items: was set to:

ColLookUpResults

Important:

To get the actual value from the Results that were put in the Collection

use the following Syntax:  

For Galleries:   ThisItem.Value.<fieldname>
For buttons/Text Field output:  First(collectionName).Value.<fieldname>

Example to Set a variable based on a result:
UpdateContext({varLookedUp:Concatenate("Found:",First(colLookUpResults).Value.UserLogin)});

For my Stored procedure that does not have a parameter it looks like this:

ClearCollect(colProcGalleryResultsCollection,
         Table(CityGarage.dbouspPowerAppsTestProcedureOnlyForGallery().ResultSets.Table1));

One thing you may see on your variables page is results with Question marks.  

I’m currently not sure why that happens but it’s possibly due to not knowing how to display the values – I mostly ignore

those for now knowing it’s in preview.    See the example below for what you may see.

When you receive the results, make sure you enclose them in the Table Function.

If you don’t put the values in a “table” using Table() you’ll see the results such as the ones below

Benefits of Stored Procedures:

From what I’ve seen the 2000 record limitation is removed when using stored procedures, however you may still

want to adhere to some limit just for performance reasons since it would really add weight to memory and bandwidth.

Ideas when using stored procedures:

• Use Try Catch blocks for Errors and error handling
• Consider using an error or Logging table with a predefined Schema
• Consider using a logging level (in a database table) to handle logging

I personally use a logging table for all my apps and stored procedures, it provides me a real-time dashboard of errors, activities and I also use it to manage versioning.

On all my apps I use a “Global version” that I assign to the app and set in the App.OnStartup

Then on a database table I have an “AppVersions” table in which I indicate what the current version is, the application name and the Date it was set.   

If I want to force a version or notify the user of updates, then I just need to compare versions and then provide an on-screen display indicating there is an update available (or in the case of "Breaking changes" prevent them from using the application until they upgrade – for updates that have incompatibilities like Schema updates on Tables or SharePoint).

I personally use and enjoy Azure Application Insights but I’ve seen latency on that and when real-time monitoring or pro-active alerts are needed that’s when I switched to using database tables "ie:a Logging table" for that and that's been very useful and

almost invaluable, slight overhead but in terms of support and troubleshooting I wouldn't be without it.    Using logging levels

can help "right-size" how much it's used.

Why Use Stored Procedures?

• Shift Database layer logic, and filters onto the database server for efficiency and speed
• Remove barriers for record limits
• Use a common layer of programmability so logic is in one location verses dispersed, this reduces maintenance.
• In a sense you’re creating a small “Model-View-Controller” pattern -   having your PowerApp being the view – with options and selections but then shifting other aspects to stored procedures, reduces complexity and redundancy sometimes.   

Lastly Here's an example of some idea's to improve your Stored Procedure - Error handling should "always" be included and not

left to chain down to the client to manage.

Lastly, here's an example of a way to improve error handling/exception handling 

USE [Your Database Name]
CREATE PROCEDURE [dbo].[uspPowerAppsTestProcedureOnly]
                        @userID int   -- my parameter in this case
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE     INT,
	        @ROWS     INT,
			@ErrorMsg varchar(2500),
			@LogMsg   varchar(2500),
			@LogInfo  varchar(2500);
    BEGIN TRANSACTION
    BEGIN TRY
       SELECT [UserId]
       FROM [Messages].[Users]
	   Where UserId = @userID
   COMMIT TRANSACTION
END TRY
-- This may be more useful for CRUD transactions (create(insert), update, delete)
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
    ROLLBACK TRANSACTION
END CATCH

SELECT =@@ERROR, @Rows=@@ROWCOUNT
IF @Rows!=1 OR !=0
BEGIN
    SET @ErrorMsg='ERROR 20, ' + ISNULL(OBJECT_NAME(@@PROCID), 'unknown') 
                               + ' - unable to ???????? the ????.'
    IF @@TRANCOUNT >0
    BEGIN 
        ROLLBACK
    END
    SET @LogInfo=ISNULL(@LogInfo,'')+'; '+ISNULL(@ErrorMsg,'')+
        +', Error='+@Error
        +', Rows='+@Rows
    INSERT INTO MyLogTable (...,Message) VALUES (....,@LogInfo)
    RETURN 20
END
END

Enjoy!!