ExecuteNonQuery always returns -1

Question

I have created a stored procedure for deleting record. In this stored procedure I am first checking for the usage of data which I am going to delete. If it is being used, then the stored procedure will return -2 otherwise it deletes the record.

But the problem is that even the record exists its return -1 instead of -2. I have also set the NOCOUNT OFF but don't know where is the problem.

I know this question is already answered by setting NOCOUNT OFF but its not working for me

ALTER PROCEDURE [dbo].[spDeletePIDNumber]
    @Id int
AS
BEGIN
    SET NOCOUNT OFF;

    -- Insert statements for procedure here
    if(exists(select * from tblBills where PID = @Id))
    begin
        return -2
    end
    else
    begin
        Delete from HelperPIDNumber 
        where Id = @Id
    end
END

public int DeletePIDNumber(int Id)
{
        try
        {
            int result = 0;

            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.connection))
            {
                var cmd = new SqlCommand("spDeletePIDNumber", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", Id);

                conn.Open();
                result = cmd.ExecuteNonQuery();
            }

            return result;
        }
        catch
        {
            throw;
        }
    }

`ExecuteNonQuery` doesn't return the result of the procedure you're calling. Did you read the documentation? — Luaan, Nov 13 '16 at 12:30
Instead of relying on the return value you can map an output parameter to get the return. — Geethanga, Nov 13 '16 at 12:36

score 3 · Accepted Answer · edited May 23 '17 at 12:01

From the ExecuteNonQuery documentation:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

Having SET NOCOUNT ON; in your procedure explicitely tells to SQL Server not to return a row count. In that case the return of the ExecuteNonQuery function is -1.

Also if the procedure does not affect any rows, it will not return a row count either even if NOCOUNT is OFF. In that case the return will also be -1.

What you appear to want to do is get the return value of the stored procedure. You will not get that from the result of ExecuteNonQuery. Please refer to this question on StackOverflow: Getting return value from stored procedure in ADO.NET

score 2 · Answer 2 · edited Nov 13 '16 at 12:53

2

Generally ExecuteNonQuery will return number of affected records. It will return -1 in two cases:

When SET NOCOUNT ON has been set. From your code, its clear, you have SET NOCOUNT OFF and so this is not an issue at your case.
If number of affected rows is nothing, it will return -1. In your case, it looks like you are checking the data exists from one table tblBills and delete from another table HelperPIDNumber. So there is more chance there will be no matching record and nothing deleted.

Please check the point # 2 above.

if( exists(select * from tblBills where PID = @Id))
begin
    return -2
end
else
begin
    Delete from HelperPIDNumber where Id = @Id
end

edited Nov 13 '16 at 12:53

marc_s

732,580
175
1,330
1,459

answered Nov 13 '16 at 12:36

Aruna

11,959
3
28
42

In a nutshell the return value of the ExecuteNonQuery is a system reserved value, you should not try to interfere with that. If you want to send back custom value to the C# code from your SP, use an output parameter and extract the value. – Geethanga Nov 13 '16 at 12:41
@Geethanga That's not my code :-) I have copied the user's code and highlighted the possible issue from the particular piece of code – Aruna Nov 13 '16 at 12:43

score 0 · Answer 3 · answered Nov 13 '16 at 13:21

0

use cmd.ExecuteScalar() instead of cmd.ExecuteNonQuery() ascmd.ExecuteNonQuery() return only the number of affected rows and not the value you are selecting.

answered Nov 13 '16 at 13:21

Ali Ashkar Ct

50
5

Nope. From the documentation: `Executes the query, and **returns the first column of the first row in the result set returned by the query**. Additional columns or rows are ignored.`. This is not the same as the return **value**. – TT. Nov 13 '16 at 13:25
`return -2` can be changed to `SELECT -1` – Ali Ashkar Ct Nov 13 '16 at 14:58
The OP could do that, but that is generally a bad idea. For stored procedures it is usually a good idea to have the resultset(s) returned be of the same format (ie same columns in the same order). – TT. Nov 13 '16 at 15:01

ExecuteNonQuery always returns -1

3 Answers3