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.
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 TSQL
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)
In the feedback, I was asked for a Connect item. Here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525653