Microsoft GraphQL API to Execute Stored Procedures

Problem

We have developed a data platform using Microsoft Fabric. We are exposing some of the data from our data warehouse in a third-party app using the Microsoft GraphQL API. Everything works fine when we want to query or update data, but there are also some stored procedures from the database that we would like to use as well. Is this possible with the GraphQL API?

Solution

In a couple of previous tips, we explained the GraphQL API for Microsoft Fabric:

In this tip, we’ll explore how you can leverage existing stored procedures within the GraphQL API. Note: Microsoft Fabric is a SaaS product with a monthly release cadence, which means that features or the user interface might change between the writing and the publication of this article. Furthermore, the GraphQL API of Microsoft Fabric is in public preview at the time of writing.

Execute Stored Procedures with the Microsoft GraphQL API

Stored procedures in Fabric can be created in either the warehouse or SQL Analytics endpoint of a lakehouse. A stored procedure can modify data or return a result set. When data is returned, the stored procedure will be exposed as a query. When data is modified, it will be exposed as a mutation. (Learn more about mutations in the tip CRUD operations in Microsoft Fabric by using GraphQL API Mutations.)

Stored Procedure as a Query and Microsoft GraphQL API

The sample data used in this tip is from the Wide World Importers sample database. At some point, you could load this sample data when you used the Copy Data wizard in a pipeline, but it seems this option was removed.

sample data in pipeline in the Microsoft GraphQL API

If you want to follow along with the examples, you can try loading the on-premises sample database into your Fabric environment.

In the warehouse, create the following stored procedure:

CREATE PROCEDURE dbo.StockItemSalesReport 
    @stockitemkey INT
AS
SELECT
     c.SalesTerritory
    ,d.CalendarMonthLabel
    ,Quantity   = SUM(Quantity)
    ,TotalSales = SUM(TotalIncludingTax)
FROM dbo.fact_sale f
JOIN dbo.dimension_city     c ON f.CityKey          = c.CityKey
JOIN dbo.dimension_date     d ON f.InvoiceDateKey   = d.[Date]
JOIN dbo.dimension_customer u ON f.CustomerKey      = u.CustomerKey
WHERE f.StockItemKey = @stockitemkey
GROUP BY c.SalesTerritory, d.CalendarMonthLabel,d.CalendarMonthNumber
ORDER BY c.SalesTerritory, d.CalendarMonthNumber;

The logic is too complex to do in a GraphQL query. (You can use relationships as explained in the tip How to use Relationships with the Microsoft Fabric GraphQL API, but aggregating data might be harder). You can put the query in a view (views are also included in the GraphQL schema as queries), but in our use case, we want to use a parameter to get only a subset of the data. This is always more efficient than retrieving all data and then filtering in the application.

In the GraphQL editor, go to Get Data and select the warehouse that contains the stored procedure.

If some database objects were already included in the GraphQL schema, they will already be selected. The stored procedure should be added to the list as well.

When you select the procedure, you won’t get a data preview as the procedure needs a parameter. Click Load.

Once the stored procedure is loaded, it should be visible in the GraphQL schema under Queries:

Begin to write your query in the editor. Autocomplete suggests the stored procedure automatically:

It also knows which columns can be found in the result set:

columns of the stored proc in autocomplete

The following query will execute the stored procedure with a parameter value of 204 and return a couple of columns:

query{
   executeStockItemSalesReport(stockitemkey: 204) {
       SalesTerritory
      ,CalendarMonthLabel
      ,Quantity
      ,TotalSales
   }
}

It’s also possible to not hard code the parameter value in the query but to use a variable in the GraphQL query itself. In the following screenshot, we named this variable s, with a data type of Int.

The query then becomes:

query($s: Int){
    executeStockItemSalesReport(stockitemkey: $s) {
        SalesTerritory
       ,CalendarMonthLabel
       ,Quantity
       ,TotalSales
    }
}

The actual value of the variable can be specified in the right-hand pane of the editor:

If the stored procedure returns multiple result sets, only the first one will be used. For example, let’s expand our stored procedure with some “debug messages”:

ALTER PROCEDURE dbo.StockItemSalesReport 
    @stockitemkey INT
AS
BEGIN
SELECT ProcMessage = 'Stored proc starting!';
 
SELECT
     c.SalesTerritory
    ,d.CalendarMonthLabel
    ,Quantity   = SUM(Quantity)
    ,TotalSales = SUM(TotalIncludingTax)
FROM dbo.fact_sale f
JOIN dbo.dimension_city     c ON f.CityKey          = c.CityKey
JOIN dbo.dimension_date     d ON f.InvoiceDateKey   = d.[Date]
JOIN dbo.dimension_customer u ON f.CustomerKey      = u.CustomerKey
WHERE f.StockItemKey = @stockitemkey
GROUP BY c.SalesTerritory, d.CalendarMonthLabel,d.CalendarMonthNumber
ORDER BY c.SalesTerritory, d.CalendarMonthNumber;
 
SELECT ProcResult = 'Query Success!';
END

When we refresh the GraphQL schema (by going back to the Get data wizard and re-adding the stored procedure), we can see that only the column from the first result set is known:

Executing the old query (with the columns of the second result set) will give an error:

query returns an error when we reference columns that are not from the first result set

Stored Procedure as a Mutation and Microsoft GraphQL API

For this part, we are reusing the sample data from the tip named CRUD operations in Microsoft Fabric by using GraphQL API Mutations. There, we had a table tracking articles and their publishing status:

Suppose we have a report showing the articles and their status to an end user. The app has the functionality for a user to delete an article, for example, to clean up articles that were incorrectly entered. Therefore, the following stored procedure was created to delete a row from the table with the articleID as a parameter:

CREATE PROCEDURE dbo.DeleteArticle
    @articleID INT
 AS
 BEGIN
    DELETE FROM dbo.Articles
    WHERE articleID = @articleID;
 END

The stored procedure can be added to the GraphQL schema using the Get Data wizard, like before.

Fabric GraphQL will recognize the stored procedure as a mutation.

With the following query, we can execute our stored procedure through a GraphQL mutation:

mutation{
  executeDeleteArticle(articleID: 3) {
     result
  }
}

We can verify that the row with articleID 3 is actually deleted from the table:

It’s possible to get a result back with the stored procedure. Let’s add a SELECT statement at the end of the stored procedure, which displays a message:

ALTER PROCEDURE dbo.DeleteArticle
    @articleID INT
 AS
 BEGIN
    DELETE FROM dbo.Articles
    WHERE articleID = @articleID;
 
    SELECT Result = CONCAT('Row with articleID ', @articleID, ' has been deleted!')
 END

When we execute the query again after refreshing the GraphQL Schema (and replacing ‘result’ with ‘Result’ since the query is case sensitive), we get the following:

stored proc as mutation but with a result set

However, if we add a SELECT statement before the DELETE statement, the stored procedure will still be added as a mutation. Now, the first result set is returned but not the result from after the DELETE statement.

ALTER PROCEDURE dbo.DeleteArticle
    @articleID INT
 AS
 BEGIN
    SELECT ProcMessage = 'Stored proc starting!';
 
    DELETE FROM dbo.Articles
    WHERE articleID = @articleID;
 
    SELECT Result = CONCAT('Row with articleID ', @articleID, ' has been deleted!')
 END

We now need to change Result to ProcMessage in the query to get it to work.

Conclusion

In this tip, we’ve shown how to use stored procedures as either a query or a mutation in the Microsoft Fabric GraphQL API. If there’s only a SELECT statement, the stored procedure will be regarded as a query. Once there’s a DML statement like UPDATE or DELETE in the stored procedure, it will be regarded as a mutation. If there are multiple result sets, only the first one will be used.

Next Steps

For more Fabric tips, check out this overview.
In the Microsoft Ignite session about the Microsoft Fabric GraphQL API, you can view a demo about using stored procedures and parameters.

Resources

Community

Subscribe