title: Stored Proc returning one row, recordset or output parameters?
body: <p>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.</p>
  
  <p>Are there any real benefits over using one vs the other?  </p>
  
  <pre><code>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
  </code></pre>