CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlreturn
Kees C. Bakker
Kees C. Bakker

Reputation: 33391

What is better to return a single value in TSQL stored procedure: a RETURN or an OUTPUT?

I have a small stored procedure that needs to return a count of records (a single value). What is better to use? A RETURN to return the result or an OUTPUT parameter to return the result?

I'm using C# / .Net to 'talk' to SQL server.

Upvotes: 3

Views: 1740

Answers (3)

Alok
Alok

Reputation: 274

I will suggest to use OUTPUT parameter because it is less costly. Return can be used here as you are returning count (int type) but that is not advisable as return is used for status or error.

Upvotes: 0

aF.
aF.

Reputation: 66697

If you already know that the execution of your SQL statement will return a single row of data, then you should always prefer stored procedures with output parameters in place of single-row select statements.

There are several advantages of using stored procedures over embedding simple SQL statements in your application code. On top of all those advantages, what you save is instead of opening a recordset for fetching the data, you simply call a stored procedure with output parameters. When you use a recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.

I think that return and output work in a similar way. But, in general, return is most commonly used to return a status result or error code from a procedure.

Upvotes: 1

gbn
gbn

Reputation: 432271

Use an output parameter.

It is less resource intensive to deal with a scalar parameter than a dataset via SELECT (or an OUTPUT clause etc) in the client

And RETURN is limited to int datatype. This doesn't matter here, but generally RETURN would be used for status and errors

Upvotes: 5

Related Questions