SQL: Stored Procedures - Time for a real contract?

Increasingly, developers are using tools that try to automate code generation when dealing with databases. Stored procedures have been a thorn in the side of this. Mostly that’s because it’s difficult to obtain the metadata that is really needed.

You should not need to be able to read the code of a stored procedure to know how to use it. And that includes the common exceptions it might throw. None of the existing tools for dealing with this currently do what’s needed.

RowSets

Most automated tools try to work out what rowsets can come back from the sprocs. The Entity Framework, LINQ to SQL and many others use SET FMTONLY ON to try to determine what might come back from the sproc. This is a flawed mechanism as it returns one set of metadata for every potential code path through the sproc. It really only works for the most trivial sprocs ie: single statements that perform basic CRUD operations.

The first thing that is needed is a way to express the sets of rows that might be returned. This should be part of the definition of the sproc. It should include the ability to express multiple sets of rows. Some tools only work with the first rowset but that isn’t sufficient because it has become quite common for people to build sprocs that return multiple rowsets to avoid network round trips. I should be able to say something like:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
          OrderDetails(AnotherColumn INT, YetAnotherColumn INT, 
                       EvenYetAnotherColumn GEOGRAPHY),
EXECUTE AS (execution options here if needed)

Note that I think the rowsets should be able to be named and I also threw in the need for CREATE OR ALTER. Please, please, please can we have this !!!

Exceptions

For a client to work effectively with a server, it should have knowledge of the potential exceptions that might be thrown by that code. This should also be available in the metadata of the sproc. I think it could be included like this:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
          OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
                      EvenYetAnotherColumn GEOGRAPHY),
EXCEPTIONS NoSuchCustomer(50020,'No such Customer'),
           DuplicateOrder(50022,'That order already exists'),
EXECUTE AS (execution options here if needed)

Clearly, other exceptions could occur in an unplanned way but client code should be able to be configured automatically to deal with potentially expected errors. For example, a code generation tool could automatically build a skeleton error-handling routine for errors that it already knows could exist.

Contract

Clearly, this is all then heading towards having a contract for a sproc. When you have multiple people (or worse multiple teams of people) building parts of an application, it is really important to have a contract at the interface point. Perhaps the contract itself should have a name ie: something like:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails
(
    ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
         OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
                      EvenYetAnotherColumn GEOGRAPHY),
    EXCEPTIONS NoSuchCustomer(50020,'No such Customer'),
               DuplicateOrder(50022,'That order already exists')
),
EXECUTE AS (execution options here if needed)

Enforcement in T-SQL

I’d love to have a situation where some of this is enforced as much as possible within T-SQL. For example, given the declarations above, I’d love to see a situation where a SELECT statement that doesn’t match one of the rowsets in the metadata or a RAISERROR with an error number not in the list of declared exceptions was enough to make the creation or alteration of the sproc fail. Perhaps any sproc with a declared CONTRACT could have these sorts of restrictions or we could have a word like ENFORCED:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED
(
    ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
         OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
                      EvenYetAnotherColumn GEOGRAPHY),
    EXCEPTIONS NoSuchCustomer(50020,'No such Customer'),
    DuplicateOrder(50022,'That order already exists')
),
EXECUTE AS (execution options here if needed)

Return values

I’ve been thinking further about how return values should be incorporated into a contract and initially thought it should be something like this:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED
(
    ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
         OrderDetails(AnotherColumn INT, YetAnotherColumn INT, 
                      EvenYetAnotherColumn GEOGRAPHY),
    RETURNS INT,
    EXCEPTIONS NoSuchCustomer(50020,'No such Customer'),
               DuplicateOrder(50022,'That order already exists')
),
EXECUTE AS (execution options here if needed)

I thought the values could be RETURNS INT or RETURNS NULL, but on reflection (no pun intended), I realized that in many cases it is necessary to resort to documentation to know what a stored procedure return value is. That would be eased if the return value also had a name as part of its metadata. So perhaps a more complete contract would look like:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED
(
    ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
         OrderDetails(AnotherColumn INT, YetAnotherColumn INT, 
                      EvenYetAnotherColumn GEOGRAPHY),
    RETURNS OrderCount(INT),
    EXCEPTIONS NoSuchCustomer(50020,'No such Customer'),
               DuplicateOrder(50022,'That order already exists')
),
EXECUTE AS (execution options here if needed)