Skip to main content
Version: v2.x

MS SQL Server: Stored Procedures

Available on: Cloud Professional, Cloud Enterprise, Self-Hosted Enterprise

What are stored procedures?​

Supported from

Stored procedures are supported from v2.26.0.

Stored procedures can be used to track MS SQL Server stored procedures and execute them via the Hasura GraphQL Engine.

SQL Server stored procedures are built-in or user-defined Transact-SQL statements that can be used to encapsulate some custom business logic or extend the built-in SQL functions and operators.

Stored procedures support is a Cloud and Enterprise feature of Hasura.

Supported features

Currently, only read-only stored procedures are supported, and Hasura aggregations or relationships are not supported at this time.

Example: Execute a built-in stored procedure​

We’ll start with an example. Let’s use this new feature to execute a built-in stored procedure from our Hasura API. If you’d like some reference documentation, scroll down, and also take a look at the Logical Models documentation.

For our case, we would like to get some information about our database. Specifically, which tables are currently defined in the database. On SQL Server we can do that by executing the sp_tables stored procedure.

We can create a Logical Model representing the results set:

Click on the Logical Models tab, and on the Add Logical Model button.

Create Logical Model

Once the modal is open, fill in the form.

Create Logical Model Form

We can then track a stored procedure that returns that result set. Additionally, we can add arguments which can be passed to the stored procedure from the GraphQL API. We'll include the table_type arguments which can be used to filter tables, system tables, and views.

Validation during tracking is not currently supported

Currently, stored procedures are not checked against the Logical Model to validate that they return the expected result set or that the arguments match with the stored procedure's arguments. This means that if there's a mismatch between the database stored procedure and the Logical Model or the arguments, an error will be thrown when running a query.

Click on the Stored Procedures tab, and on the Track Stored Procedures button.

Track Stored Procedure

Next, fill in the form, choosing the Logical Model created in the previous step:

Stored Procedure Form

All that’s left is for us to make a GraphQL query to select the tables which are currently defined in the database:

query sp {
  sp_tables(args: { table_type: "'TABLE'" }, limit: 3) {
    TABLE_QUALIFIER
    TABLE_OWNER
    TABLE_NAME
    TABLE_TYPE
  }
}

When we run this GraphQL query, we get the following results:

{
  "data": {
    "sp_tables": [
      {
        "TABLE_QUALIFIER": "master",
        "TABLE_OWNER": "dbo",
        "TABLE_NAME": "MSreplication_options",
        "TABLE_TYPE": "TABLE"
      },
      {
        "TABLE_QUALIFIER": "master",
        "TABLE_OWNER": "dbo",
        "TABLE_NAME": "spt_fallback_db",
        "TABLE_TYPE": "TABLE"
      },
      {
        "TABLE_QUALIFIER": "master",
        "TABLE_OWNER": "dbo",
        "TABLE_NAME": "spt_fallback_dev",
        "TABLE_TYPE": "TABLE"
      }
    ]
  }
}

Next, we'll look at the process of tracking a stored procedure in more detail.

Tracking a stored procedure​

Step 1. Create a Logical Model​

In order to represent the structure of the data returned by the query, we first create a Logical Model.

Permissions and Logical Models

Note that this Logical Model has no attached permissions and therefore will only be available to the admin role. See the Logical Model documentation for information on attaching permissions.

Click on the Logical Models tab, and on the Add Logical Model button.

Create Logical Model

Once the modal is open, fill in the form with the name of the Logical Model and the fields that will be returned by the stored procedure.

Step 2. Track a stored procedure​

Once the Logical Model is defined, we can use it to define the query:

Click on the Stored Procedures tab, and on the Track Stored Procedures button.

Track Stored Procedure

Next, fill in the form, choosing the Logical Model created in the previous step as the return type.

Arguments​

The stored procedure can take arguments, which are specified in the metadata. When making a GraphQL query, the arguments are specified using the args parameter of the query root field. If the stored procedure does not take arguments, the args parameter should be omitted from the GraphQL query.

Using the stored procedure​

You can make a GraphQL request using the specified root field name just as you would any other GraphQL query. When making a query, the arguments are specified using the args parameter of the query root field.

query {
  <stored procedure name>(
    [args: {"<argument name>": <argument value>, ...},]
    [where: ...,]
    [order_by: ..., distinct_on: ...,]
    [limit: ..., offset: ...]
  ) {
    <field 1>
    <field 2>
    ...
  }
}

Currently running a stored procedure has the following caveats:

  • The stored procedure must currently be read-only.
  • The return type of the query must match with the Logical Model.

Query functionality​

Just like tables, stored procedures generate GraphQL types with the ability to further break down the data. You can find more information in the relevant documentation for filtering, sorting, and pagination.

Mutations​

Currently, only read-only stored procedures are supported. All stored procedures are run in a read-only transaction where supported to enforce this constraint.

A future release will allow mutations to be specified using stored procedures.

Permissions​

stored procedures will inherit the permissions of the Logical Model that they return. See the documentation on Logical Models for an explanation of how to add permissions.

Start with GraphQL on Hasura for Free

  • Build apps and APIs 10x faster
  • Built-in authorization and caching
  • 8x more performant than hand-rolled APIs
Try GraphQL with Hasura
Promo
Loading...
Loading...