How to get return value from my stored procedure?

Question

Here is my SQL:

IF (SELECT Status FROM dbo.Coupon WHERE Guid = @pGuid) = 0 BEGIN UPDATE dbo.Coupon SET Status = @pStatus WHERE Guid = @pGuid RETURN 0 END ELSE RETURN 1; 

And here is my C #:

 try { DbCommand command = db.GetStoredProcCommand("upd_Coupon_p"); db.AddInParameter(command, "@pGuid", DbType.String, s); db.AddInParameter(command, "@pStatus", DbType.Byte, 1); ds = db.ExecuteDataSet(command); } 

How can I get a return value of 0 or 1 inside my code?

Answer 1

You add a return value parameter, for example:

For SqlCommand:

 parameters.Add("@retValue", DbType.Int32, ParameterDirection.ReturnValue); 

For EL, you need to use db.AddParameter () and specify ParameterDirection.ReturnValue.

In addition, as long as the row counter is enabled in your database, you can use the result from ExecuteNonQuery (), which tells you how many rows were affected in update / insert / delete / etc, for the update to be performed. This way you could handle if the affected rows were 0 (could not be found)

Answer 2

This is what I did, so just use ReturnValue, but other parts may be useful.

  var retparam = new SqlParameter("@return", System.Data.SqlDbType.Int) { Direction = System.Data.ParameterDirection.ReturnValue }; comm.Parameters.Add(retparam); comm.ExecuteNonQuery(); int ret = 0; if (retparam == null) { System.Diagnostics.Debug.WriteLine("retparam was null"); } else if (retparam.Value == null) { } else { // use reparam.Value.ToString() } 

Answer 3

What is DbCommand.ExecuteDataSet () and why you are not using ExecuteScalar ()?

Answer 4

Declare the variable as a result and get it inside the function of calling the data access section.

see code below

In a stored procedure

  @ReturnStatus int output //inside your stored procedure argument section 

In the "Data Access" section, use the following,

  AddOutParameter(.....); 

Hope this helps.