CODEWITHSUNDEEP
sql-serverstored-procedures
Paul Hadfield
Paul Hadfield

Reputation: 6136

Stored Proc returning one row, recordset or output parameters?

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

Upvotes: 4

Views: 2661

Answers (3)

APC
APC

Reputation: 146339

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.

Upvotes: 3

codingbadger
codingbadger

Reputation: 44032

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.

Upvotes: 1

Denis Valeev
Denis Valeev

Reputation: 6015

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.

Upvotes: -2

Related Questions