Stored Procedures

Introduction

Stored procedures in SQL Server are pre-compiled collections of one or more SQL statements that can be executed as a single unit. Hasura allows you to expose these stored procedures through your GraphQL API, enabling you to:

• Execute complex business logic defined in your database
• Perform multiple operations in a single transaction
• Return result sets and output parameters
• Leverage existing database procedures in your API

Adding Stored Procedures

To expose stored procedures through your GraphQL API:

1. First, ensure your stored procedures exist in your SQL Server database

2. Use the DDN CLI to introspect and add the procedures:

   # Add stored procedures to your connector configuration
   ddn connector plugin --connector my_subgraph/connector/chinook_sqlserver/connector.yaml -- update stored-procedures
   
   # Update metadata to track the procedures
   ddn connector-link update chinook_sqlserver --add-all-resources

The stored procedures will be added to the $.metadata.storedProcedures section of your configuration.

Configuration

Each stored procedure requires configuration to specify its arguments and return type. Here's an example:

{
  "storedProcedures": {
    "GetArtistsByName": {
      "name": "GetArtistsByName",
      "schema": "dbo",
      "arguments": {
        "Name": {
          "name": "Name",
          "type": "varchar",
          "nullable": "nullable",
          "isOutput": false,
          "description": "The name of the artist to lookup"
        }
      },
      "returns": {
        "CustomerId": {
          "name": "CustomerId",
          "type": "int",
          "nullable": "nonNullable",
          "description": "The ID of the artist"
        },
        "Phone": {
          "name": "Phone",
          "type": "varchar",
          "nullable": "nonNullable",
          "description": "The phone number of the artist"
        },
        "TotalPurchases": {
          "name": "TotalPurchases",
          "type": "int",
          "nullable": "nonNullable",
          "description": "The total number of purchases for the artist"
        }
      },
      "description": "Retrieves detailed information about an artist"
    }
  }
}

Return Types

When stored procedures are first introspected, the returns field will be null. You must manually configure the return type based on what your stored procedure returns.

The return type configuration should match the structure of the result set(s) returned by your procedure. Each field in the result set should be defined in the returns object with its appropriate type information.

Updating Stored Procedures

To update the configuration of existing stored procedures, use the --overwrite flag:

ddn connector plugin --connector my_subgraph/connector/sqlserver/connector.yaml -- update stored-procedures --overwrite

This will refresh the stored procedure definitions while preserving any manual configurations you've added.

Usage

Once configured, you can execute stored procedures through your GraphQL API:

query {
  GetCustomerDetails(CustomerId: 123) {
    CustomerId
    Name
    Email
  }
}

The response will contain the result set returned by the stored procedure:

{
  "data": {
    "GetArtistsByName": {
      "ArtistId": 123,
      "Name": "John Smith",
      "Phone": "123-456-7890",
      "TotalPurchases": 10
    }
  }
}

Best Practices

1. Always configure return types explicitly to ensure proper GraphQL schema generation
2. Include descriptions for procedures, arguments, and return fields to improve API documentation
3. Consider using output parameters for procedures that need to return multiple result sets
4. Test procedures to ensure they work as expected through the GraphQL API
5. Follow SQL Server best practices for stored procedure performance and security