CODEWITHSUNDEEP
.netsql-serverreturn-value
CRice
CRice

Reputation: 12567

How to return result of Sql Server stored procedure to .Net

What is the best way to return the result of the execution of a SQL Server stored procedure back to the .NET code?

If I want to return the success or failure of a bunch of deletes in a transaction, should I 'Select' something, or maybe something like RETURN(1) or RETURN(0), or do I communicate number of rows affected?

Then will this be the return value for ExecuteNonQuery()?

Upvotes: 0

Views: 1597

Answers (3)

Chris W
Chris W

Reputation: 3314

You'll frequently see the return value used to indicate a problem with return 0 indicating that the SP completed with success. I generally use to return information about what has happened where the error itself wouldn't generate an exception. Across most of my apps I have a standard set of return codes that I re-use.

To provide more detailed output in specific scenarios you can then use the output parameters as well

Upvotes: 1

Fung
Fung

Reputation: 7760

ExecuteNonQuery always returns the number of rows affected for the LAST insert, update or delete done in your transaction. You don't need to RETURN(1) or RETURN(0).

If you're doing a bunch of deletes in a transaction and it fails then an SqlException would be thrown so there isn't really a need to return a result per se. Just catch the exception and handle it instead.

And to answer your question, if the result you wanted is specifically the number of affected records of the last executed statement then using ExecuteNonQuery is the best. If you wanted some other more specific result then the best might be to use an SqlCommand with a return SqlParameter and return the specific value in your return parameter.

Upvotes: 2

Cody C
Cody C

Reputation: 4797

Yes, if you use ExecuteNonQuery(), it will return the number of rows affected. If you want to specify a value to return in your stored procedure, you can use ExecuteScalary().

You can also use an output parameter to return values. In the SQL procedure, declare the parameter as an output parameter (ex. @ret int output). In the .NET code, set the direction of the parameter to output. After the execution line, you can check this value by reading the ".Value" property

Upvotes: 1

Related Questions