In C # how to get return value from stored procedure using ExecuteNonQuery

Question

I have the following query:

create proc [dbo].[DeleteParts] @TransNo nvarchar (6), @fpart nvarchar(25) AS DECLARE @Returns BIT SET @Returns = 1 BEGIN TRY BEGIN TRANSACTION DELETE FROM PARTABLE WHERE TransNo = @TransNo and fpart = @fpart COMMIT END TRY BEGIN CATCH Print 'Delete failed' SET @Returns = 0 -- Any Error Occurred during Transaction. Rollback IF @@TRANCOUNT > 0 ROLLBACK -- Roll back END CATCH RETURN @Returns 

It compiles fine.

In C #, I want to fulfill this request and get the return value.

My code is as follows:

 using(System.Data.SqlClient.SqlCommand deletecommand = this._connection.CreateCommand()) { deletecommand.CommandText = "DeleteParts"; deletecommand.CommandType = System.Data.CommandType.StoredProcedure; deletecommand.Parameters.AddWithValue("@TransNo", ItemSODBOM.SONO); deletecommand.Parameters.AddWithValue("@fpart", ItemSODBOM.fbompart); string ReturnValue = deletecommand.ExecuteNonQuery().ToString(); } 

This does not give me any error, but instead returns the number of rows affected, I want to return 1 or 0.

Example: if a successful deletion is deleted, then return 1, and if it does not work, return 0.

Any help with source code would be greatly appreciated.

Thanks,

Pradeep

Answer 1

You need a parameter with Direction set to ParameterDirection.ReturnValue

Something like:

 SqlParameter returnParameter = deleteCommand.Parameters.Add("RetVal", SqlDbType.Int); returnParameter.Direction = ParameterDirection.ReturnValue; ... deleteCommand.ExecuteNonQuery(); ... int returnValue = (int) returnParameter.Value; 

The stored procedure should return this return value to you, of course:

 create proc [dbo].[DeleteParts] @TransNo nvarchar (6), @fpart nvarchar(25) AS DECLARE @Returns BIT SET @Returns = 1 ... RETURN @Returns 

Answer 2

I do not see that you are returning a value. Add a Return statement to return any value from the stored procedure.

Answer 3

ExecuteNonQuery will return the number of rows affected, but NOT the data (this is why this is not a query). So it won’t bring anything.

It may be useful to read:

http://www.dreamincode.net/forums/topic/76434-executenonquery-with-output-parameters/

You will need to use another mechanism to output your data - what about an ExecuteReader with an output parameter?

Answer 4

ExecuteNonQuery() returns @@ROWCOUNT , which you cannot set. So you really cannot use it to return values.

Answer 5

Typically, the result is returned as a string, just like a regular select query. You can get it using a reader or adapter.

Answer 6

you must specify the type of output in stored procedures such as

 @IDout [int] output 

then add this parameter

 SPParamReturnCollection sp = new SPParamReturnCollection(); sp.Add(new SPParams { Name = "IDout", ParamDirection = ParameterDirection.Output, Type = SqlDbType.Int }); 

Answer 7

 IF EXISTS(SELECT TransNo FROM [PARTABLE] WHERE TransNo = @TransNo and fpart = @fpart BEGIN DELETE FROM PARTABLE WHERE TransNo = @TransNo and fpart = @fpart SELECT @TransNo AS RETURNVAL END ELSE BEGIN SELECT 0 AS RETURNVAL END 

Answer 8

Naming the variable "@Returns" does not magically return its value, you should really return the value.

You cannot return a bit value; the return value is always an integer. If you want to send the bit value back, you will have to use the output parameter.

Add a return to the procedure:

 create proc [dbo].[DeleteParts] @TransNo nvarchar (6), @fpart nvarchar(25) AS DECLARE @Returns INT SET @Returns = 1 BEGIN TRY BEGIN TRANSACTION DELETE FROM PARTABLE WHERE TransNo = @TransNo and fpart = @fpart COMMIT END TRY BEGIN CATCH Print 'Delete failed' SET @Returns = 0 -- Any Error Occurred during Transaction. Rollback IF @@TRANCOUNT > 0 ROLLBACK END CATCH RETURN @Returns 

Add a parameter with the direction ReturnValue to get the return value:

 int returnValue; using(System.Data.SqlClient.SqlCommand deletecommand = this._connection.CreateCommand()) { deletecommand.CommandText = "DeleteParts"; deletecommand.CommandType = System.Data.CommandType.StoredProcedure; deletecommand.Parameters.AddWithValue("@TransNo", ItemSODBOM.SONO); deletecommand.Parameters.AddWithValue("@fpart", ItemSODBOM.fbompart); var returnParameter = deletecommand.Parameters.Add("@ret", SqlDbType.Int); returnParameter.Direction = ParameterDirection.ReturnValue; deletecommand.ExecuteNonQuery(); returnValue = (int)returnParameter.Value; }