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!!
-
LenC
on:
Exploring Access Management using Power Automate F...
-
powerappnewbie4
on:
Securing Attachment Control in Power Apps: Validat...
-
ramanujama
on:
Implementing Offline Capability in Power Apps
- AndyHoll on: Convert Office Files to PDF in Power Apps
- AndyHoll on: Embedding Office Files as PDFs
- allamram on: CICD using Deployment Pipelines in Power Platform
- hemomsusubodh on: Running Stored Procedures directly with PowerFX
-
powerappnewbie4
on:
How To count rows for gallery with more than 100 r...
-
jela
on:
Multilingual support in Canvas Apps Using Microsof...
- Poweruser1211 on: ADVANTAGES AND DISADVANTAGES OF USING POWER FX TO ...