Inconsistent value returned by Output Parameter

[xnetdevph]

I've been running this scenario for a week and hardly find help on this unusual.

My code is running in C# language that interfaces with SQL 2005. I have a typical stored procedure that returns value based on the result of the execution. Using the SQLCommand, this value is obtained with the setting ParameterDirection to Output. For certain time, the program is running as expected. However, time comes that this variable suddenly returns a "null" value. The input parameters passed to this object is the same value as entered prior to this incident but suddenly it returns a "null" value.

The stored procedure being used is coded that should only send 1, -1 & -2. No zero & no null value were indicated on the code.

I tried to put try-catch & raiserror to be able to catch this error, however neither raise errors.

Need help desperately. Thanks.

[Hack]

Show me your stored procedure and what gets passed to it.

How long is "However, time comes"?

[xnetdevph]

Here's my stored procedure. The "time comes" is unpredictable. The shortest that this anomaly appears is 40 seconds.



CREATE PROCEDURE [dbo].[spCheckValidWorkOrder]
@WORK_ORDER_NUMBER VARCHAR(50),
@RESULT_VALUE int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @RESULT int

SET @RESULT = -2

-- Insert statements for procedure here
IF EXISTS(SELECT 1 FROM WORK_ORDERS WHERE WORK_ORDER_NUMBER = @WORK_ORDER_NUMBER)
BEGIN
SET @RESULT = 1
END
ELSE
BEGIN
SET @RESULT = -1
END

if @@ERROR <> 0
BEGIN
RAISERROR('Error encountered in spCheckValidWorkOrder',10,1)
END

--return @RESULT
SET @RESULT_VALUE = @RESULT

END

Originally Posted by Hack

Show me your stored procedure and what gets passed to it.

How long is "However, time comes"?