ExecuteNonQuery() returns -1 always

Question

I am using a stored procedure to insert some value in table.

CREATE PROCEDURE [dbo].[Sp_InsertValue]
@Val1 as nvarchar(50)
@Val2 as nvarchar(50)
as
BEGIN
    IF NOT EXISTS(SELECT * FROM @mytable WHERE ID=@Val1)
    INSERT INTO @mytable VALUES(@VAL2)
END

I am using ExecuteNonQuery() to call this stored procedure in ASP.NET using C#. It works fine, no issues, it inserts values if they don't exist. The issue is that cmd.ExecuteNonQuery() always return -1. I expect if a record is inserted, it should return 1, and 0 if not, right?

Answer 1

Check that you don't have SET NOCOUNT ON in your stored procedure. This will stop the number of affect rows be returned. Literally 'NoCount' is ON.

Default response will always be '-1' in this situation.

Answer 2

ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

1. if you have SET NOCOUNT ON before executing insert/update/delete statements in your stored procedure.

   ExecuteNonQuery returns value -1

2. In your entire stored procedure when there is no chances to execute Insert/Update/Delete statements due to any conditions like

Declare @x int = 5; If x > 5 Begin Performing Insert/Update/Delete operation. End

--other type of statements

In the above example, it will not reach to inside if condition so No chances of executing insert/update/delete statements in the entire stored procedure so in this case also

ExecuteNonQuery returns value -1