Stored Procedure Contracts - Return Values

Yesterday’s blog post on the need for contracts for stored procedures caused a lot of comments and email. One of the most interesting comments came from Jamie Thomson regarding return values. Jamie’s totally correct on this. Return values should be part of any contract.

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)

The idea is that you could have a value like RETURNS SomeName(INT) or RETURNS NULL where there is no return value.

The feedback item is here: https://feedback.azure.com/d365community/idea/3b9b8fea-e74f-ec11-a819-0022484bf651

2010-01-20