Connect to SQL Server from Power Apps

You can connect to SQL Server in either Azure or an on-premises database.

Note

Newly created SQL data sources are no longer prefixed with [dbo] like in previous versions of Power Apps.

For more information, see Common issues and resolutions for Power Apps.

Generate an app automatically

Depending on which Power Apps interface you're using, reference the new look or the classic look to build an app.

  1. Sign in to Power Apps.

  2. From the Home page, select either the single-page gallery or three screen mobile option:

    • To create a single-page gallery app with a responsive layout, choose either:
      • Start with data > Select external data > From SQL.
      • Start with a page design > Gallery connected to external data > From SQL.
    • To create a three screen mobile app, select Start with an app template > From SQL.
  3. Select your SQL connection and then select a table. To choose a different connection, select the ... overflow menu to switch your connection or create a new SQL connection.

    Note

    Only one connection is shown at a time.

  4. When you're done, select Create app.

Call stored procedures directly in Power Fx (preview)

You can directly call SQL Server stored procedures from Power Fx by turning on the SQL Server stored procedure preview switch.

Note

Output parameters aren't supported.

  1. Go to Settings > Upcoming features > Preview.
  2. Search for stored procedures.
  3. Set the toggle to On for SQL Server stored procedures.
  4. Save and reopen the app.

Screenshot that shows the SQL Server stored procedures toggle set to On.

When you add a SQL Server connection to your app, you can now add tables and views or stored procedures. This feature also works with secure implicit connections.

Screenshot that shows lists of tables, views, and stored procedures available to be added to your app.

If you don't immediately see your stored procedure, it's faster to search for it.

Once you select a stored procedure, a child node appears and you can designate the stored procedure as Safe to use for galleries and tables. If you check this option, you can assign your stored procedure as an Items property for galleries for tables to use in your app.

Enable this option only if:

  1. There are no side effects to calling this procedure on demand, multiple times, whenever Power Apps refreshes the control. When used with an Items property of a gallery or table, Power Apps calls the stored procedure whenever the system determines a refresh is needed. You can't control when the stored procedure is called.
  2. The amount of data you return in the stored procedure is modest. Action calls, such as stored procedures, do not have a limit on the number of rows retrieved. They aren't automatically paged in 100 record increments like tabular data sources such as tables or views. So, if the stored procedure returns too much data (many thousands of records) then your app might slow down or crash. For performance reasons you should bring in less than 2,000 records.

Important

The schema of the return values of the stored procedure should be static. Meaning that it does not change from call to call. For example, if you call a stored procedure and it returns two tables then it should always return two tables. If the schema of the results are dynamic then you should not use it with Power Apps. For example if you call the stored procedure and it sometimes returns one table and sometimes returns two tables then it will not work correctly in Power Apps. Power Apps requires a static schema for this call.

Example

When you add a stored procedure, you might see more than one data source in your project.

Screenshot that shows SQL data sources.

To use a stored procedure in Power Apps, first prefix the stored procedure name with the name of connector associated with it and the name the stored procedure. 'Paruntimedb.dbonewlibrarybook' in the example illustrates this pattern. Note also that when Power Apps brings the stored procedure in, it concatenates the full name. So, 'dbo.newlibrarybook' becomes 'dbonewlibrarybook'.

Remember to convert values appropriately as you pass them into your stored procedure as necessary since you're reading from a text value in Power Apps. For example, if you're updating an integer in SQL you must convert the text in the field using 'Value()'.

Calling stored procedures directly.

You can access a stored procedure for the Items property of a gallery after you declare it safe for the UI. Reference the data source name and the name of the stored procedure followed by 'ResultSets'. You can access multiple results by referencing the set of tables returned such as Table 1, Table 2, etc.

For example, your access of a stored procedure off of a data source named 'Paruntimedb' with a stored procedure named 'dbo.spo_show_all_library_books()' will look like the following.

Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1

This populates the gallery with records. However, stored procedures are an addition of action behaviors to the tabular model. Refresh() only works with tabular data sources and can't be used with stored procedures. Then you need to refresh the gallery when a record is created, updated, or deleted. When you use a Submit() on a form for a tabular data source it effectively calls Refresh() under the covers and updates the gallery.

To get around this limitation, use a variable in the OnVisible property for the screen and set the stored procedure to the variable.

Set(SP_Books, Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1);

And then set the 'Items' property of the gallery to the variable name.

SP_Books

Then after you create, update, or delete a record with a call to the stored procedure, set the variable again. This updates the gallery.

Paruntimedb.dbonewlibrarybook({   
  book_name: DataCardValue3_2.Text, 
  author: DataCardValue1_2.Text,
    ...
});
Set(SP_Books, Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1);

Known issues

SQL data sources no longer add a [dbo] prefix to the data source name

The [dbo] prefix doesn't serve any practical purpose in Power Apps as data source names are automatically disambiguated. Existing data sources aren't affected by this change, but any newly added SQL data sources don't include the prefix.

If you need to update a large number of formulas in one of your apps, the Power Apps Source File Pack and Unpack Utility can be used to do a global search-and-replace.

Note

Starting in version 3.21054, we'll automatically update broken legacy name references to the new data source name after reading the data source.

Next steps