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

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

This compiles perfectly fine.

In C#, I want to execute this query and get the return value.

My code is as below:

using(System.Data.SqlClient.SqlCommand deletecommand = t开发者_StackOverflow社区his._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();
}

It does not give me any error but instead it is returning number of rows affected, I want to return 1 or 0.

Example: if delete operation success then return 1 and if it fails then return 0.

Any help with source code would be appreciated.

Thanks,

Pradeep

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;

You Stored Procedure needs to return this return value of course:

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

I don't see that you are returning the value. Please add the Return statement to return any value from the stored proc.

ExecuteNonQuery will return the number of rows affected but NOT data (that's why its a non-query). So it won't bring anything back.

This might be useful to read:

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

You'll need to use a different mechanism to get your data out - how about ExecuteReader with an output parameter?

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

Usually the result is returned as a row, like a normal select query would be. You can get at it using a reader or adaptor.

you must specify output type in stored procedures like this

@IDout                  [int] output

then add this parameter

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

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

Naming a variable "@Returns" doesn't magically return it's value, you have to actually return the value.

You can't return a bit value, the return value is always an integer. If you want to sent a bit value back, you would have to use an output parameter instead.

Add a return statement 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 receive 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;
}