SQL Server 2008 Stored Proc suddenly returns -1

Question

I use the following stored procedure from my SQL Server 2008 database to return a value to my C#-Program

ALTER PROCEDURE [dbo].[getArticleBelongsToCatsCount] 
@id int
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @result int;

   set @result = (SELECT COUNT(*) FROM art_in_cat WHERE child_id = @id);
   return @result;
END

I use a SQLCommand-Object to call this Stored Procedure

public int ExecuteNonQuery()
{
  try
  {
    return _command.ExecuteNonQuery();
  }
  catch (Exception e)
  {
    Logger.instance.ErrorRoutine(e, "Text: " + _command.CommandText);
    return -1;
  }
}

Till recently, everything works fine. All of a sudden, the stored procedure returned -1. At first, I suspected, that the ExecuteNonQuery-Command would have caused and Exception, but when stepping through the function, it shows that no Exception is thrown and the return value comes directly from return _command.ExecuteNonQuery();

I checked following parameters and they were as expected: - Connection object was set to the correct database with correct access values - the parameter for the SP was there and contained the right type, direction and value

Then I checked the SP via SQLManager, I used the same value for the parameter like the one for which my C# brings -1 as result (btw. I checked some more parameter values in my C' program and they ALL returned -1) but in the manager, the SP returns the correct value.

It looks like the call from my C# prog is somehow bugged, but as I don't get any error (it's just the -1 from the SP), I have no idea, where to look for a solution.

Answer 1

It looks like you are returning the status of the Count query execution when you really want the value of COUNT (*).

I think it should be like this:

ALTER PROCEDURE [dbo].[getArticleBelongsToCatsCount] 
@id int,
@NumberOfRows int OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

    SELECT @NumberOfRows = COUNT(*) FROM art_in_cat WHERE child_id = @id;

END

Answer 2

From MSDN ExecuteNonQuery:

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no 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. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

ExecuteScalar is probably what you want.

Your SP could then be this:

ALTER PROCEDURE [dbo].[getArticleBelongsToCatsCount] 
@id int
AS
BEGIN
   SET NOCOUNT ON;

   SELECT COUNT(*) FROM art_in_cat WHERE child_id = @id
END

Answer 3

ExecuteNonQuery will return the number of rows affected by an INSERT, UPDATE or DELETE operation, not the return value of a stored procedure. If it was working in the past, is there a possibility that data was being modified by your query?

To get the return value of a stored procedure, add a parameter to your command with ParameterDirection.ReturnValue. After calling ExecuteNonQuery(), that parameter will be populated with the return value of the stored procedure.

Answer 4

ExecuteNonQuery returns a value of -1 for commands that don't affect any rows. See the documentation at MSDN. You need to check the value of the output parameter @result.

Answer 5

return _command.ExecuteNonQuery();

should be

return _command.ExecuteScalar();

Reason being that it is a query you are running, a query that returns a single value. ExecuteNonQuery() assumes there is nothing being returned at all (such as running an UPDATE query).