What is better to return single value from stored

问题:

I need to create a stored procedure that needs to return a count of some records. I'm using .Net to read the result.

I can use an OUTPUT parameter to return the value or I could do a select count(*) in the stored procedure and use a SqlCommand.ExecuteScalar to read it.

What is better and why?

回答1:

Check out this MSDN article: Performance Comparison: Data Access Techniques

The article shows in the performance test for GetOrderStatus that the performance between OUTPUT parameter and ExecuteScaler is the same for retreiving a single value, but ExecuteScalar requires less code.

Here are some other interesting thoughts about not going for the OUTPUT parameter: What's wrong with output parameters. I like the thought of Output params break the fundamental idea of a function in that post.

回答2:

Definitely - OUTPUT parameter is the fastest and rightest way

回答3:

To add a thought - ExecuteScalar will return status code if SP does not mention otherwise. It seems to be good practice to keep that around.