SQL Server 2008 Stored Proc suddenly returns -1

Posted by aaginor on Stack Overflow See other posts from Stack Overflow or by aaginor
Published on 2009-11-04T15:47:40Z Indexed on 2010/03/14 17:45 UTC
Read the original article Hit count: 207

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.

© Stack Overflow or respective owner

Related posts about c#

Related posts about stored-procedures