Stored Proc returning one row, recordset or output parameters?

Question

Ignoring ORM / NHibernate, etc. Consider the common requirement to pass in a Unique ID and return data from a single row (if the ID is found). The two ways to do this would be to return a record set or to use output parameters. Stored proc would be called from .NET C# code - so from that point of view one requires the set up / reading of additional parameters with an ExecuteNonQuery, whilst the other requires an ExecuteScalar and the accessing / reading of a DataReader.

Are there any real benefits over using one vs the other?

CREATE PROC GetUserByRecordSet
  @UserID UniqueIdentifier
AS
BEGIN
   SELECT 
      ID,
      Name,
      EmailAddress
   FROM
      [User]
   WHERE
      id = @UserID
END
GO

CREATE PROC GetUserByOutputParams
  @UserID UniqueIdentifier,
  @Name NVARCHAR(255) OUTPUT,
  @EmailAddress NVARCHAR(500) OUTPUT
AS
BEGIN
   SELECT 
      @Name =Name,
      @EmailAddress = EmailAddress
   FROM
      [User]
   WHERE
      id = @UserID
END
GO

Answer 1

If you only return ID of a user, in order to get that user information your ORM would need to do another round-trip to the database to get additional information about that user.

I would just set up proper mapping between your entity and your sql tables and get all the necessary fields, instead of returning IDs.

But suppose we have a procedure, that returns hundreds of IDs, surely you don't want to return whole records of it if you do some intricate filtering of that data based on ids. So, ymmv.

Answer 2

I would return the single row and use a SqlDataReader to access the information. After all that single row may turn in to multiple rows.

Answer 3

The main difference between the two approaches is flexibility. If you want to change the columns returned there is less work in changing a procedure which returns a recordset.