Returning Key Values from Stored Procedures

Question

I wonder which is better practice when I need to return the primary key value of a newly inserted record from a SQL stored procedure. Consider the following implementations:

As Return Value

CREATE PROCEDURE [CreateRecord] ( @value NVARCHAR(128) )
AS
BEGIN
    INSERT [Records] ( [Value] ) VALUES ( @value );
    RETURN SCOPE_IDENTITY();
END

Obviously this only works for INT keys, but it takes advantage of pre-existing functionality. Also, only works when inserting a single record. I have seen this done in few places, but I generally don't like it.

As Output Parameter

CREATE PROCEDURE [CreateRecord] ( @value NVARCHAR(128), @id INT = NULL OUTPUT )
AS
BEGIN
    INSERT [Records] ( [Value] ) VALUES ( @value );
    SET @id = SCOPE_IDENTITY();
END

Works for any type of key, including multi-column keys, but still only works when inserting a single record. This is my preferred method unless I absolutely have to insert more than one record at a time.

As XML Output Parameter

CREATE PROCEDURE [CreateRecord] ( @value NVARCHAR(128), @xmldata XML = NULL OUTPUT )
AS
BEGIN
    DECLARE @inserted TABLE ( Id INT );

    INSERT [Records] ( [Value] )
    OUTPUT [inserted].[Id]
    INTO @inserted ( [Id] )
    VALUES ( @value );

    SET @xmldata = (SELECT [Id] FROM @inserted FOR XML AUTO);
END

This is similar to the previous solution, but allows multiple records to be returned. Trying to parse the output would be a huge pain, but I suppose this would be suitable for some applications. For example, if a stored procedure can insert into several different tables, a single output parameter can describe all the inserted records.

As Result Set

CREATE PROCEDURE [CreateRecord] ( @value NVARCHAR(128) )
AS
BEGIN
    DECLARE @inserted TABLE ( Id INT );

    INSERT [Records] ( [Value] )
    OUTPUT [inserted].[Id]
    INTO @inserted ( [Id] )
    VALUES ( @value );

    SELECT * FROM @inserted;
END

Superficially, this may be appealing, especially if your calling this SP from ADO, since getting the result set from the command is very straight forward. To use this SP from SQL, however, you have to write an INSERT-EXEC script, which is somewhat inelegant, in my opinion (but still a lot better than parsing XML).

Perhaps there are other methods that I'm not even aware of. What is the best method of returning the newly inserted key value from this kind of stored procedure? Are there any guidelines for deciding which is the best method?

Answer 1

I think you've already answered your own question to some degree: there are multiple options for returning data from a stored procedure and they all behave differently so there is no single answer. But there are definitely some general guidelines and common practices.

RETURN is a control-of-flow statement: its primary use is to exit a procedure, not to return data. But of course return codes are a very common feature of many programming languages, and the documentation makes that usage pattern explicit:

A stored procedure can return an integer value called a return code to indicate the execution status of a procedure

So you should use this not to return data, but to return metadata about the execution of the procedure. Usually this means did it succeed or fail, and if it failed what was the error status. Whether or not you actually want to use this feature is entirely up to you, of course.

Output parameters are the standard way of returning scalar values, and are especially useful when the procedure is called by another piece of TSQL code, because as you mentioned sharing data between stored procedures is not always easy, especially if you want to share a result set. The only obvious reason not to use them for scalar values is if your client code - perhaps an ORM or DAL - and/or coding practices make it easier to consume a result set (more on that below).

I'm not going to comment on your XML solution because I'm not an XML guy, but it seems very awkward and unless your client code could only consume XML then I'm not sure what the point would be.

A result set is more flexible than an output parameter because it can return multiple rows (obviously), so if you need a result set then it's the only choice anyway.

(Strictly speaking that isn't true, because you can return a cursor as an output parameter, but avoiding cursors whenever possible is a good rule in TSQL and I personally don't see any advantage over returning a result set. Other than it being easier for calling code in TSQL to consume the cursor.)

That all means that the 'correct' answer to your question is to use an output parameter when returning one key value, or a result set when returning more than one. But, that will add some complexity to your calling code because it needs to know which procedures return output parameters and which ones return result sets (or both).

For that reason it's not uncommon for organizations to make explicit coding rules that say that stored procedures must always return data in result sets (usually one and only one per procedure). Then developers don't need to worry about it, they just always write code to consume result sets, even if some result sets always have just one row. This is an important consideration, because developers' time is usually by far the most expensive component of any application, so anything you can do to make development simpler, faster and less error-prone is very worthwhile.

Perhaps there's a performance difference between using output parameters and result sets, but if you're worried about that then set up a test and gather some performance data (it's almost always better to test and measure performance yourself before asking someone else's opinion, since that person's general answer may turn out to be wrong for your specific case). And even if there is some performance difference, if it isn't big enough to impact your application then you can ignore it and use whichever mechanism you prefer.