Stored Procedure Support

[yorek]

Summary

Allow stored procedure defined in the database to be exposed as REST or GraphQL

Motivation

Stored procedures are widely used, simplify data access, provide abstraction over base tables, and may encapsulate even complex business logic definition. They can also be used as an additional security mechanism to reduce exposed surface area. All these points help to make solutions simpler and in general helps DAB to support existing databases and scenarios.

Functional Specifications

CRUD Support

Stored Procedure will support any CRUD action in the sense that it will be responsibility of the developer - or whoever is defining the configuration file - to properly configure the allowed actions to make sure the related exposed entity will allow actions that are compatible with the stored procedure. In fact, a stored procedure can potentially do any operation on data, and DAB will have no way to know what operation is being done, but it will only be able to see the returned result set.

Relationship Support

At this time, entities backed by a stored procedure will not be able to be used in relationships with other entities nor establish relationships with other entities.

Stored Procedure requirements

A stored procedure may not return any result set.
A stored procedure may return more than one result set, but all result sets except the first will be ignored by DAB.

Stored Procedure Parameters

Only scalar parameters are supported in DAB. In GraphQL each parameter will be exposed as a query or mutation parameter. For REST each parameter will be exposed as a query string parameter.

Prior Work

Stored Procedure Support has been originally discussed in the following issues: #624, #757, #869

[yorek]

Feature Readiness Checklist

• RFC Discussed and Approved
• Engine updated as per RFC
• Documentation updated to describe View usage
• JSON Schema updated to support View specification

[Aniruddh25]

Issue #869 also discussed the Stored Procedure Support on GraphQL

[yorek]

For Azure SQL a stored procedure will be supported only if it returns a result set that can be analyzed via sys.dm_exec_describe_first_result_set_for_object

[abhishekkumams]

Thanks @yorek , I tested it out and we are able to figure out the columns :

I tried it out with different stored Procedure as shown in the Snippet above:

1. Get_Books- > Get all the rows from Books table.
2. Count_test_Books - > Count the number of rows in the Books table.
3. Insert_test_book - > insert a new row in the books table.

Note:
In GraphQL Context, the first two would be Query operation, while the last one is a mutation operation.

As Discussed in the Stored-Procedure for GraphQL Design review discussion:

1. We will create the result using sys.dm_exec_describe_first_result_set_for_object.
2. We won't give any result for mutation query if there are no columns in the result_set, as shown for the third stored-procedure in the image above.
3. Also, we would restrict users to specify either READ or WRITE action for Stored-Procedure, or else show validation error.