Using stored procedures in Data API builder

Stored procedures can be exposed as REST or GraphQL endpoints in DAB. This is useful for scenarios that involve custom logic, filtering, validation, or computed results not handled by simple tables or views.

Configuration

To expose a stored procedure:

• Set source.type to "stored-procedure"
• Set source.object to the fully qualified procedure name
• Define the parameters (with types) if necessary
• Set rest.methods (for example, "GET", "POST") or rest: false
• Set graphql.operation to "query" or "mutation", or omit to default to "mutation"
• Grant permission using the "execute" action

CLI example

dab add GetCowrittenBooksByAuthor \
  --source dbo.stp_get_all_cowritten_books_by_author \
  --source.type "stored-procedure" \
  --source.params "searchType:s" \
  --permissions "anonymous:execute" \
  --rest.methods "get" \
  --graphql.operation "query"

Configuration example

"GetCowrittenBooksByAuthor": {
  "source": {
    "type": "stored-procedure",
    "object": "dbo.stp_get_all_cowritten_books_by_author",
    "parameters": {
      "searchType": "s"
    }
  },
  "rest": {
    "methods": [ "GET" ]
  },
  "graphql": {
    "operation": "query"
  },
  "permissions": [
    {
      "role": "anonymous",
      "actions": [ "execute" ]
    }
  ]
}

Parameter types (used in CLI and config)

These codes define the type only, not default values.

REST support

• Supports only GET and POST
• Defaults to POST if methods is omitted
• Sends parameters via query string with GET
• Sends parameters via JSON body with POST
• Disables REST for a stored procedure if "rest": false is set

Example requests

GET /api/GetCowrittenBooksByAuthor?author=asimov

POST /api/GetCowrittenBooksByAuthor

{
  "author": "asimov"
}

GraphQL support

• Requires graphql.operation to be "query" or "mutation"
• Fields are autoprefixed with execute, for example, executeGetCowrittenBooksByAuthor
• Parameters are passed as GraphQL arguments

Example GraphQL

query {
  executeGetCowrittenBooksByAuthor(author: "asimov") {
    id
    title
  }
}

Limitations

• Only the first result set is returned
• Pagination, filtering, and ordering aren't supported
• Relationships aren't supported
• Requires metadata from sys.dm_exec_describe_first_result_set
• Can't return a single item by key
• No parameter-level authorization