⭐ [Enhancement]: Stored Procedure Parameters

[JerryNixon]

What is it?

• Support proc/param metadata.
• Support optional parameters.
• Support output parameters.
• Support error-provider parameters.

Output scenario

This demonstrates an output parameter.

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT
AS
    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

Note how the result set from the SELECT statement is combined with the output parameter itself.

Current Configuration

Current configuration supports parameters and their default values - only.

{
  "entities": [
    {
      "<entity-name>": {
        ...
        "source": {
          ...
          "parameters": {
              "<parameter-name>": "<parameter-default>",
              "<parameter-name>": "<parameter-default>",
              "<parameter-name>": "<parameter-default>"
            }
...

Future Configuration

New object type provides more flexibility. This is an additive/alternative change.

{
  "entities": [
    {
      "<entity-name>": {
        ...
        "metadata": {
          "description": "<string>" (optional default: null)
        },
        "source": {
          ...
          "parameters": [ // current hierarchy position 
           {
              "name": "<parameter-name>",
              "alias": "<string>" (optional default: <parameter-name>),
              "description": "<string>" (optional default: null),
              "data-type": "<string>" (optional default: <string>), 
              "required": "true | false" (optional default: true),
              "default": "<string>" (optional default: null)
              "kind": "input | output | input-output | error-provider", (optional default: input)
           }
...

Description

Metadata Description is used to enhance both the OpenAPI and GraphQL Schema.

Maybe this means views and tables get description. We will have to see.

CLI impact

Though this may change to align with ongoing work, this is pretty close.

• dab update <entity-name> --metadata.description "value"
• dab update <entity-name> --parameters "<parameter-name>" --name "value"
• dab update <entity-name> --parameters "<parameter-name>" --alias "value"
• dab update <entity-name> --parameters "<parameter-name>" --description "value"
• dab update <entity-name> --parameters "<parameter-name>" --data-type "value"
• dab update <entity-name> --parameters "<parameter-name>" --required "value"
• dab update <entity-name> --parameters "<parameter-name>" --default "value"
• dab update <entity-name> --parameters "<parameter-name>" --kind "value"

How do we delete a parameter from the CLI?

• dab update <entity-name> --parameters "<parameter-name>" --delete

Output parameter

This supports both SELECT and OUTPUT params at the same time.

Example (page count)

CREATE PROCEDURE [dbo].[GetBooksPaginated]
    @PageSize INT,
    @PageNumber INT,
    @PageCount INT OUTPUT
AS
BEGIN

    DECLARE @TotalCount INT;
    SELECT @TotalCount = COUNT(*) FROM dbo.Books;
    SET @PageCount = CEILING(@TotalCount * 1.0 / @PageSize);

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

END;

Output payload (GraphQL)

TBD

Output payload (REST)

{
    "value": [
        {
            "Id": 1,
            "Name": "William Shatner",
            "BirthYear": 1931
        }
    ],
    "output-params": [
        {
            "Name": "PageCount",
            "Value": 123
        }
    ],
    "errors": [] // see below
}

Database parity

Database	Supports Output Params
SQL Server	Yes
MySQL	Yes
PostgreSQL	Yes
Cosmos DB (SQL)	No (Return values through JavaScript)

Error-Provider

Today, stored procedures cannot raise an error in a controlled way.

Today's error format (REST)

{
    "error": {
        "code": "DatabaseOperationFailed",
        "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.",
        "status": 500
    }
}

Today's error format (GQL)

{
  "errors": [
    {
      "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.",
      "extensions": {
        "code": "DatabaseOperationFailed"
      }
    }
  ]
}

Future error format (REST)

The original error remains - it will return the First() from the new errors array.

{
    "value": [],
    "output-params": [],
    "errors": [
      {
          "code": "DatabaseOperationFailed", // optional
          "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.", // required
          "status": 500 // optional
      }
    ]
}

Future error format (GQL)

No change.

Raising errors versus returning errors

This section shows how returning errors is better than raising them. Reasons:

1. Allows multiple errors.
2. Keeps execution flow.
3. Consistent format for APIs.
4. Customizable messages.

Raising errors

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT
AS
BEGIN
    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

    IF @count < 10
        RAISERROR('The number of books is less than 10. Operation aborted.', 16, 1);
END;

(versus) Returning errors

Effectively, an error-provider is an output parameter with a JSON payload of errors.

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT,
    @ErrorMessage NVARCHAR(MAX) OUTPUT  
AS
BEGIN

    IF @count < 10
    BEGIN

        -- error 
        DECLARE @ErrorTable TABLE (code NVARCHAR(50), message NVARCHAR(255), status INT);

        INSERT INTO @ErrorTable (code, message, status)
        VALUES ('DatabaseOperationFailed', 'The number of books is less than 10. Operation aborted.', 500);

        SELECT @ErrorMessage = (SELECT * FROM @ErrorTable FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

        RETURN; -- Abort 

    END

    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

END;

Error result

{
    "errors": [
      {
          "code": "DatabaseOperationFailed",
          "message": "The number of books is less than 10. Operation aborted.",
          "status": 500
      }
    ]
}

Questions

1. Can errors be returned WITH data? Yes.
2. Should status in an error impact the returned HTTP status? Return 200 (REST & GQL)
3. Let's make sure we log the non-generic error and return the generic error.
4. Custom errors are not censored.

Related Issues to Close

• #1841
• #1826
• #1843
• #2346
• #2222
• #1748
• #1844

[pingu2k4]

I would love to have this. My primary use case currently, is requesting paginated data from a SP for a datagrid, but also wanting to know the total count (also, given filter and sort data).

Am currently achieving this with 2 SP's, and 2 queries - but being able to set an output param with the total count for the given filter would be much more preferable.

[JerryNixon]

Should this be REST only? @michaelstaib GraphQL have output params on the roadmap?

[abhishekkumams]

Good to have a list of validations that needs to be performed with the above CLI commands.

[abhishekkumams]

Note: Individual value returned by the output parameter will always be scalar, so there's no need for us to have an array of value inside output params. No database supports a table type return value in output parameters today.

[abhishekkumams]

@JerryNixon, Can you add some more details on each of these values :

"parameters": [ // current hierarchy position 
{
...
    "kind": "input | output | input-output | error-provider", (optional default: input)
}