SQL Server 2008 Stored Proc suddenly returns -1
- by aaginor
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.
