THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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 Connect item is here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525653 

Published Wednesday, January 20, 2010 9:23 AM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Greg, you and Jamie seem to be mixing up the usage of RETURN and OUTPUT.  I don't see any mention here of OUTPUT parameters, which are in my mind a more important aspect of the contract, since they return data of varying data types, whereas a RETURN value is just supposed to return execution status, and be limited to INT.

January 19, 2010 5:45 PM
 

Greg Low said:

Hi Aaron,

I'm thinking that OUTPUT parameters are already provided for adequately in the current definitions and metadata. But I think the lack of a name for a return value is an issue.

Regards,

Greg

January 19, 2010 6:06 PM
 

Lars said:

Greg,

What you are describing is not new, it became mainstream in object oriented programming two decades ago. We had interfaces in early '90s. But before that we had catches which actually catch errors, and functions which run without huge performance hit; we had both in the '80s.

So how about fixing the disfunctional basics, the flaky catch and the slow UDF, before moving on to develop exciting new features?

January 19, 2010 6:08 PM
 

AaronBertrand said:

Greg, what is the value of giving the return value a name, or requiring that one be given?  Since I often see:

RETURN 0;

or just:

RETURN;

Can you explain exactly what would be gained from having a contract where the name of the return value was known?  Or even of specifying its data type (what do you expect to gain from allowing data types other than INT)?

January 19, 2010 10:39 PM
 

AaronBertrand said:

Lars, I agree, the UDF should certainly be a high-level work item, and I can only imagine there are improvements coming for the half-baked TRY/CATCH.

January 19, 2010 10:41 PM
 

Greg Low said:

Hi Lars,

We all agree that there are other problematic areas of the product. But that's not an argument for fixing the ones that can be fixed.

Regards,

Greg

January 19, 2010 11:02 PM
 

Greg Low said:

Hi Aaron,

Yes, I often see procs that just return a zero or nothing but I've also seen procs where the return value has a specific meaning ie: where people have used it as a form of output parameter. If that's the case, then you should be able to specify what on earth it is.

For example, I've seen insert procs where (for better or worse) the newly-inserted ID was returned as the return value of the proc. In that case, it would be good to have the metadata reflect that that's what the value actually is. I've seen others where update procs return the number of rows updated and yet others where a positive value is good and a negative value indicates some form of failure.

Regards,

Greg

January 19, 2010 11:05 PM
 

Aaron Bertrand said:

Greg, sorry but I still disagree with you about return values.  Just because you've seen them used to return data (which is what OUTPUT parameteres are for), doesn't make it a good idea, one we should encourage, and certainly not one which we should change the product to accommodate.  IMHO.  I have always believed that return values are used solely and explicitly for returning execution status / error codes and still don't agree that we should modify metadata functionality to suit the way they are misused by a portion of users.

January 19, 2010 11:26 PM
 

Greg Low said:

Hi Aaron,

We'll have to differ on that one. Even if you only use them for returning an execution status, wouldn't it be good if the metadata indicated that that's what it was?

Regards,

Greg

January 20, 2010 6:11 AM
 

Alexander Kuznetsov said:

Hi Greg,

I hear what you are saying: "fixing the ones that can be fixed".

Do you imply that the unfinished error handling cannot be finished at all?

If yes, why?

January 20, 2010 9:17 AM
 

AaronBertrand said:

If RETURN were used consistently the way it was designed, instead of being misused, there wouldn't be any need to indicate that it is returning status, because there shouldn't be any other options.  Your use of RETURN OrderCount indicates that it is returning *data* and to me this is not appropriate.  Why not use an OUTPUT parameter for that data?

January 20, 2010 9:26 AM
 

Greg Low said:

Hi Aaron,

Do you think there should be a consistent standard for indicating execution status?

There are two schools of thought on returning execution status. The problem with having an execution status that indicates a problem is that it can be ignored. In general, I prefer to have an exception raised when an error occurs as the client can't just ignore that.

Regardless, if the return value represents *something*, even if it's an execution status, I'd prefer it to have a name that says what it is.

Regards,

Greg

January 20, 2010 4:57 PM
 

AaronBertrand said:

Greg,

I don't think RETURN always has to be used.  If you prefer always using a RAISERROR, then you can do that, and you haven't changed your need around naming RETURN values.  I like the flexibility that you can use RETURN values to consume and report errors from the parent level, instead of having to include all error logging etc. in every single module.

January 21, 2010 9:54 AM
 

Aaron Bertrand said:

Give us easier to read execution plans Michelle Ufford ( @SQLFool ) recently asked for help pinpointing

January 22, 2010 3:46 PM
 

The Bit Bucket (Greg Low): IDisposable said:

Temp tables are visible within the scope where they are declared but also in sub-scopes. This means that

January 4, 2012 7:07 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement