Execute Stored Procedures in the Microsoft GraphQL API

By:   |   Updated: 2024-11-12   |   Comments   |   Related: > Microsoft Fabric


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 Fabric 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.

Stored Procedures and the Fabric 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

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

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.

connect to warehouse

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.

select stored proc from list

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

no data preview available

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

stored proc is added to the schema

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

stored proc and autocomplete

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
   }
}
execute the stored procedure

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.

use parameter in graphql query

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:

query with variable

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:

only first result set is used

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

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:

articles sample data

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.

add new proc to schema

Fabric GraphQL will recognize the stored procedure as a mutation.

stored proc added as mutation

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

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

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

row is deleted

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.

mutation with first result set

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-11-12

Comments For This Article

















get free sql tips
agree to terms