{"id":39631,"date":"2024-11-07T17:40:34","date_gmt":"2024-11-07T12:10:34","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=39631"},"modified":"2024-11-08T12:50:03","modified_gmt":"2024-11-08T07:20:03","slug":"executing-sql-server-stored-procedures-with-power-fx-in-power-apps","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2024\/11\/executing-sql-server-stored-procedures-with-power-fx-in-power-apps\/","title":{"rendered":"Executing SQL Server stored procedures with Power FX in Power Apps"},"content":{"rendered":"<p><img decoding=\"async\" class=\"alignnone size-full wp-image-39647\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps.png\" alt=\"Power Apps\" width=\"2100\" height=\"1200\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps.png 2100w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-300x171.png 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-1024x585.png 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-768x439.png 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-1536x878.png 1536w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-2048x1170.png 2048w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Power-Apps-660x377.png 660w\" sizes=\"(max-width: 2100px) 100vw, 2100px\" \/><\/p>\n<p>A stored procedure in SQL is a pre-defined collection of SQL commands stored within the database, optimized to enhance execution efficiency and support consistent reuse across applications. It can include various operations such as SELECT, INSERT, UPDATE, or even complex logic involving loops and conditionals. Stored procedures are essential for modularizing code, enhancing performance, and providing better security in database management systems.<\/p>\n<p>Microsoft has introduced a new feature that enables direct calling of <strong>SQL<\/strong> stored procedures in <a href=\"https:\/\/www.inogic.com\/services\/microsoft-power-platform\/microsoft-power-apps-canvas-app\/\" target=\"_blank\" rel=\"noopener\"><strong>Canvas<\/strong> <strong>Apps<\/strong><\/a> using Power FX. Previously, invoking stored procedures from Power Apps required the use of Power Automate flows, which added complexity and performance overhead.<\/p>\n<p><strong>Prerequisites:<\/strong><\/p>\n<ul>\n<li><strong>Power Apps<\/strong>: Microsoft Dataverse access with permission to create apps.<\/li>\n<li><strong>SQL<\/strong> Stored Procedure: An existing <strong>SQL<\/strong> stored procedure that you want to call\/execute in Power Apps.<\/li>\n<\/ul>\n<p><strong>Note:<\/strong> A data gateway is required when connecting to an on-premises <strong>SQL<\/strong> Server.<\/p>\n<p>Let\u2019s explore how to call <strong>SQL<\/strong> Server stored procedures in <strong>Canvas<\/strong> apps:<\/p>\n<p><strong>Step 1: <\/strong>Sign in to Microsoft Dataverse i.e. <a href=\"https:\/\/make.powerapps.com\" target=\"_blank\" rel=\"noopener\">https:\/\/make.powerapps.com<\/a><\/p>\n<ul>\n<li>Create a Canvas app.<\/li>\n<li>Go to Data &gt; Add data &gt; Select <strong>SQL<\/strong> Server and provide all required details.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-39632\" style=\"border: 1px solid #000000; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps.png\" alt=\"Power FX in Power Apps\" width=\"1038\" height=\"463\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps.png 1038w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps-300x134.png 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps-1024x457.png 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps-768x343.png 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/1Power-FX-in-Power-Apps-660x294.png 660w\" sizes=\"(max-width: 1038px) 100vw, 1038px\" \/><\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-39633\" style=\"border: 1px solid #000000; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/2Power-FX-in-Power-Apps.png\" alt=\"Power FX in Power Apps\" width=\"281\" height=\"615\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/2Power-FX-in-Power-Apps.png 281w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/2Power-FX-in-Power-Apps-137x300.png 137w\" sizes=\"(max-width: 281px) 100vw, 281px\" \/><\/p>\n<ul>\n<li>After connecting, go to the Stored Procedures tab and select the stored procedure you wish to execute.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-39634\" style=\"border: 1px solid #000000; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/3Power-FX-in-Power-Apps.png\" alt=\"Power FX in Power Apps\" width=\"295\" height=\"615\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/3Power-FX-in-Power-Apps.png 295w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/3Power-FX-in-Power-Apps-144x300.png 144w\" sizes=\"(max-width: 295px) 100vw, 295px\" \/><\/p>\n<p>When you select a stored procedure, an option appears as a child node, enabling you to designate the procedure as safe for integration within galleries and tables. Enabling this option allows Power Apps to treat the procedure as an Items property, meaning it will be invoked whenever the control refreshes.<\/p>\n<p><strong>Important Considerations:<\/strong><\/p>\n<ul>\n<li><strong>No Side Effects:<\/strong> Ensure that the procedure does not have adverse effects when called multiple times.<\/li>\n<li><strong>Modest Data Volume:<\/strong> The stored procedure should return a manageable amount of data (ideally fewer than 2,000 records) to prevent performance issues, as it will not be automatically paged like other data sources.<\/li>\n<\/ul>\n<p><strong>Calling the Stored Procedure with Power FX:<\/strong><\/p>\n<ul>\n<li>You can call the stored procedure with Power FX using the following formula:<\/li>\n<\/ul>\n<p>Table( D365.dboGetCompanyAllDetails().ResultSets.Table1)<\/p>\n<ul>\n<li><strong>D365<\/strong> is the name of the <strong>SQL<\/strong> Server database.<\/li>\n<li><strong>dboGetCompanyAllDetails()<\/strong> is the stored procedure being called, with any required parameters passed within parentheses.<\/li>\n<li><strong>ResultSets.Table1<\/strong> refers to the result of the query. When multiple result sets are returned, they are typically organized as tables (e.g., Table1, Table2, etc.). This indicates that the stored procedure can return multiple tables, with <strong>Table1<\/strong> representing the first result set in the output.<\/li>\n<\/ul>\n<p>To display the results in a gallery in <strong>Canvas<\/strong> apps, use the above formula in the Gallery&#8217;s Items property:<\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-39635\" style=\"border: 1px solid #000000; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps.png\" alt=\"Power FX in Power Apps\" width=\"1231\" height=\"557\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps.png 1231w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps-300x136.png 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps-1024x463.png 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps-768x348.png 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/4Power-FX-in-Power-Apps-660x299.png 660w\" sizes=\"(max-width: 1231px) 100vw, 1231px\" \/><\/p>\n<p>You can also store the results in a collection for reuse elsewhere:<\/p>\n<p>ClearCollect(CompanyData, Table(D365.dboGetCompanyAllDetails().ResultSets.Table1))<\/p>\n<p><strong>Conclusion:<\/strong><\/p>\n<p>With these steps, <strong>SQL<\/strong> stored procedures can be efficiently executed in your <strong>Canvas<\/strong> apps using Power FX, streamlining the app development process and enhancing overall performance.<\/p>\n<p><strong> <img decoding=\"async\" class=\"alignnone size-full wp-image-39636\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2024\/11\/Canvas-App-1.gif\" alt=\"Canvas-App\" width=\"800\" height=\"200\" \/><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A stored procedure in SQL is a pre-defined collection of SQL commands stored within the database, optimized to enhance execution efficiency and support consistent reuse across applications. It can include various operations such as SELECT, INSERT, UPDATE, or even complex logic involving loops and conditionals. Stored procedures are essential for modularizing code, enhancing performance, and\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.inogic.com\/blog\/2024\/11\/executing-sql-server-stored-procedures-with-power-fx-in-power-apps\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1954],"tags":[],"class_list":["post-39631","post","type-post","status-publish","format-standard","hentry","category-canvas-apps"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/39631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=39631"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/39631\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=39631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=39631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=39631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}