[Feature Request] Support for Stored Procedure Output Parameters

[seantleonard]

Support surfacing any SP output parameters in a response for REST and GraphQL.

Initial work with the goal to support this occurred in #1440 and #1441.

Requests for this feature:

• 1841

• 1826

Quoting the text included in that issue from @falven

Why make this change?

• Adds support for OUTPUT Stored Procedure parameters from schema generation to data retrieval and return.
• Backwards compatible - if no OUTPUT parameters are defined in the Stored procedure, it will use the legacy schema.graphql schema format.
• Adds support for optional Stored Procedure parameters. They can now be excluded from the config if they are optional in the Stored Procedure.
• Fixes several other bugs I encountered.
• Closes #1429

What is this change?

Given the following Stored Procedure:

CREATE PROCEDURE [dbo].[GetBooks]
    @page INT,
    @pageSize INT,
    @totalBooks INT OUTPUT,
    @totalPages INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.Books
    ORDER BY Id
    OFFSET @pageSize * (@page - 1) ROWS
    FETCH NEXT @pageSize ROWS ONLY;

    SELECT @totalBooks = COUNT(*)
    FROM dbo.Books;

    SET @totalPages = CEILING(CAST(@totalBooks AS FLOAT) / @pageSize);
END;

Which you can query as follows:

DECLARE @totalBooks INT;
DECLARE @totalPages INT;

EXEC dbo.[GetBooks]
    @page = 1,
    @pageSize = 5,
    @totalBooks = @totalBooks OUTPUT,
    @totalPages = @totalPages OUTPUT;

SELECT @totalBooks AS "Total Books";
SELECT @totalPages AS "Total Pages";

Giving you results:

Running DAB will generate the following schema.graphql:

schema {
  query: Query
}

enum OrderBy {
  ASC
  DESC
}

input DefaultValue {
  Byte: Byte
  Short: Short
  Int: Int
  Long: Long
  String: String
  Boolean: Boolean
  Single: Single
  Float: Float
  Decimal: Decimal
  DateTime: DateTime
  ByteArray: ByteArray
}

type GetBooks {
  id: Int!
  title: String!
  year: Int
  pages: Int
}

"Represents the results of the executeGetBooks stored procedure execution."
type ExecuteGetBooksResult {
  "The GetBooks result set from the stored procedure."
  resultSet: [GetBooks!]!
  "The totalBooks InputOutput parameter from the stored procedure."
  totalBooks: Int
  "The totalPages InputOutput parameter from the stored procedure."
  totalPages: Int
}

type Query {
  "Execute Stored-Procedure GetBooks and get results from the database"
  executeGetBooks(page: Int = 1 pageSize: Int = 5 totalBooks: Int = 0 totalPages: Int = 0): ExecuteGetBooksResult!
}

enum ApplyPolicy {
  BEFORE_RESOLVER
  AFTER_RESOLVER
}

"The `Byte` scalar type represents non-fractional whole numeric values. Byte can represent values between 0 and 255."
scalar Byte

"The `Short` scalar type represents non-fractional signed whole 16-bit numeric values. Short can represent values between -(2^15) and 2^15 - 1."
scalar Short

"The `Long` scalar type represents non-fractional signed whole 64-bit numeric values. Long can represent values between -(2^63) and 2^63 - 1."
scalar Long

"IEEE 754 32 bit float"
scalar Single

"The built-in `Decimal` scalar type."
scalar Decimal

"The `DateTime` scalar represents an ISO-8601 compliant date time type."
scalar DateTime

scalar ByteArray

"The `@oneOf` directive is used within the type system definition language\n to indicate:\n\n - an Input Object is a Oneof Input Object, or\n - an Object Type's Field is a Oneof Field."
directive @oneOf on INPUT_OBJECT

directive @authorize("The name of the authorization policy that determines access to the annotated resource." policy: String "Roles that are allowed to access the annotated resource." roles: [String!] "Defines when when the resolver shall be executed.By default the resolver is executed after the policy has determined that the current user is allowed to access the field." apply: ApplyPolicy! = BEFORE_RESOLVER) repeatable on SCHEMA | OBJECT | FIELD_DEFINITION

"A directive to indicate the type maps to a storable entity not a nested entity."
directive @model("Underlying name of the database entity." name: String) on OBJECT

"A directive to indicate the relationship between two tables"
directive @relationship("The name of the GraphQL type the relationship targets" target: String "The relationship cardinality" cardinality: String) on FIELD_DEFINITION | INPUT_FIELD_DEFINITION

"A directive to indicate the primary key field of an item."
directive @primaryKey("The underlying database type." databaseType: String) on FIELD_DEFINITION

"The default value to be used when creating an item."
directive @defaultValue(value: DefaultValue) on FIELD_DEFINITION

"Indicates that a field is auto generated by the database."
directive @autoGenerated on FIELD_DEFINITION

This more closely follows the pagination-style results where rather than having a “Connected” Type with an “items” property, we instead have an “ExecuteResult” Type with a “resultSet” property, as well as one property per OUTPUT parameter. In the case above we have “totalBooks” and “totalPages” as those were the output parameters to our Stored Procedure. This makes more sense as these OUTPUT parameters are scalar results and not a part of a Stored Procedure’s Result Set.

This can now be queried as follows:

Additionally, it is backwards-compatible. If we alter the procedure as below:

ALTER PROCEDURE [dbo].[GetBooks]
    @page INT,
    @pageSize INT
    -- @totalBooks INT OUTPUT,
    -- @totalPages INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @totalBooks FLOAT;
    DECLARE @totalPages FLOAT;

    SELECT *
    FROM dbo.Books
    ORDER BY Id
    OFFSET @pageSize * (@page - 1) ROWS
    FETCH NEXT @pageSize ROWS ONLY;

    SELECT @totalBooks = COUNT(*)
    FROM dbo.Books;

    SET @totalPages = CEILING(CAST(@totalBooks AS FLOAT) / @pageSize);
END;

We can see queries still retrieve the books:

-- DECLARE @totalBooks INT;
-- DECLARE @totalPages INT;

EXEC [dbo].[GetBooks]
    @page = 1,
    @pageSize = 5
    -- @totalBooks = @totalBooks OUTPUT,
    -- @totalPages = @totalPages OUTPUT;

-- SELECT @totalBooks AS "Total Books";
-- SELECT @totalPages AS "Total Pages";

If there are no output parameters defined in the procedure, I have DAB generate the following schema:

schema {
  query: Query
}

enum OrderBy {
  ASC
  DESC
}

input DefaultValue {
  Byte: Byte
  Short: Short
  Int: Int
  Long: Long
  String: String
  Boolean: Boolean
  Single: Single
  Float: Float
  Decimal: Decimal
  DateTime: DateTime
  ByteArray: ByteArray
}

type GetBooks {
  id: Int!
  title: String!
  year: Int
  pages: Int
}

type Query {
  "Execute Stored-Procedure GetBooks and get results from the database"
  executeGetBooks(page: Int = 1 pageSize: Int = 5): [GetBooks!]!
}

enum ApplyPolicy {
  BEFORE_RESOLVER
  AFTER_RESOLVER
}

"The `Byte` scalar type represents non-fractional whole numeric values. Byte can represent values between 0 and 255."
scalar Byte

"The `Short` scalar type represents non-fractional signed whole 16-bit numeric values. Short can represent values between -(2^15) and 2^15 - 1."
scalar Short

"The `Long` scalar type represents non-fractional signed whole 64-bit numeric values. Long can represent values between -(2^63) and 2^63 - 1."
scalar Long

"IEEE 754 32 bit float"
scalar Single

"The built-in `Decimal` scalar type."
scalar Decimal

"The `DateTime` scalar represents an ISO-8601 compliant date time type."
scalar DateTime

scalar ByteArray

"The `@oneOf` directive is used within the type system definition language\n to indicate:\n\n - an Input Object is a Oneof Input Object, or\n - an Object Type's Field is a Oneof Field."
directive @oneOf on INPUT_OBJECT

directive @authorize("The name of the authorization policy that determines access to the annotated resource." policy: String "Roles that are allowed to access the annotated resource." roles: [String!] "Defines when when the resolver shall be executed.By default the resolver is executed after the policy has determined that the current user is allowed to access the field." apply: ApplyPolicy! = BEFORE_RESOLVER) repeatable on SCHEMA | OBJECT | FIELD_DEFINITION

"A directive to indicate the type maps to a storable entity not a nested entity."
directive @model("Un

derlying name of the database entity." name: String) on OBJECT

"A directive to indicate the relationship between two tables" directive @relationship("The name of the GraphQL type the relationship targets" target: String "The relationship cardinality" cardinality: String) on FIELD_DEFINITION | INPUT_FIELD_DEFINITION

"A directive to indicate the primary key field of an item." directive @primaryKey("The underlying database type." databaseType: String) on FIELD_DEFINITION

"The default value to be used when creating an item." directive @defaultValue(value: DefaultValue) on FIELD_DEFINITION

"Indicates that a field is auto generated by the database." directive @autoGenerated on FIELD_DEFINITION

Which we can query as we did before introducing OUTPUT parameter directions:

![BackwardsCompatiblePostmanResults](https://user-images.githubusercontent.com/578774/232577631-a57afa91-8f0b-46cd-b42e-ba755b3b1ccc.png)

[pingu2k4]

I would love to see this, and the example given is a perfect scenario for what we are looking to achieve.

[JerryNixon]

I would love to see this, and the example given is a perfect scenario for what we are looking to achieve.

Curious. What are you trying to achieve?

[pingu2k4]

I would love to see this, and the example given is a perfect scenario for what we are looking to achieve.

Curious. What are you trying to achieve?

I'm returning a data set that I want to query from a stored proc, but also want to know some metadata that doesn't fit within the dataset. For example I want to select 25 items from something, but I also want to know the count of how many items exist in said table. Currently doing it by calling 2 stored procs from the same gql query, but would be nicer to have it all done within a single one, and get both results that I am looking for together.