Does a stored Proc return a single row, a set of records, or output parameters?

Question

Ignoring ORM / NHibernate etc. Consider the general requirement of transmitting a unique identifier and returning data from one row (if an ID is found). Two ways to do this is to return a set of records or use the output parameters. The saved proc will be called from .NET C # code - therefore, from this point of view, setting / reading additional parameters using ExecuteNonQuery is required, while the other requires ExecuteScalar and access / reading DataReader.

Are there any real advantages over using one and 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

The main difference between the two approaches is flexibility. If you want to change the returned columns, changing the procedure that returns a set of records has less work.

Answer 2

I would return one row and use SqlDataReader to access the information. After each individual line can go to several lines.

Answer 3

If you only return the user ID to get information about the user, your ORM will need to do another round in the database to get additional information about this user.

I would just set the correct mapping between your entity and your sql tables and get all the required fields instead of returning identifiers.

But suppose we have a procedure that returns hundreds of identifiers, of course, you do not want to return entire records if you perform complex filtering of this data based on identifiers. So ymmv.