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?

Melisandra

when you execute this procedure in sql server query window can you see any records updated message in result pane? and also try returning rows affected by adding this to procedure last line - Return @@Rowcount – Prevost

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.

Nought

Thank you! I was debugging the code that treats -1 as error and could not figure out why it's happening. Microsoft doc dubiously says "When SET NOCOUNT ON is set on the connection (before or as part of executing the command, or as part of a trigger initiated by the execution of the command) the rows affected by individual statements stop contributing to the count of rows affected that is returned by this method." (learn.microsoft.com/en-us/dotnet/api/…). That made me think that it should return 0. – Lampas

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

Theorist