Question Stored Procedure doesn't appear to be returning the Returned value to C#

[tim8w]

So I have a StoredProcedure that either Updates or Inserts a record. The SP is as follows:

USE [FPY]
GO
/****** Object:  StoredProcedure [dbo].[insupd_tblMRBHistory_AB]    Script Date: 2025-01-10 8:02:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insupd_tblMRBHistory_AB]
           (@BusinessUnit char(30)
           ,@PartNum char(25)
           ,@Description varchar(50)
           ,@Category char(4)
           ,@LotNum char(25)
           ,@LocationID int
           ,@StockMRB int
           ,@ExtendedCost decimal(7,2)
           ,@TransDate datetime
           ,@MRBDays int
           ,@Closed bit)
AS
BEGIN
    UPDATE [dbo].[tblMRBHistory]
    SET        [BusinessUnit] = @BusinessUnit,
               [PartNum] = @PartNum,
               [Description] = @Description,
               [Category] = @Category,
               [LotNum] = @LotNum,
               [LocationID] = @LocationID,
               [StockMRB] = @StockMRB,
               [ExtendedCost] = @ExtendedCost,
               [MRBDays] = @MRBDays,
               [Closed] = @Closed
    WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum AND [LocationID] = @LocationID

    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO [dbo].[tblMRBHistory]
                   ([BusinessUnit],
                   [PartNum],
                   [Description],
                   [Category],
                   [LotNum],
                   [LocationID],
                   [StockMRB],
                   [ExtendedCost],
                   [TransDate],
                   [MRBDays],
                   [Closed])
             VALUES
                   (@BusinessUnit,
                   @PartNum,
                   @Description,
                   @Category,
                   @LotNum,
                   @LocationID,
                   @StockMRB,
                   @ExtendedCost,
                   @TransDate,
                   @MRBDays,
                   @Closed)
        RETURN 2 --cHANGE FROM SELECT TO RETURN
    END
    ELSE
    BEGIN
        RETURN 1--cHANGE FROM SELECT TO RETURN
    END
END

The C# code that executes the SP is as follows:

iInserted = Convert.ToInt32(myCmd.ExecuteScalar());

When the line is executed in C#, it ALWAYS sets iInserted to 0. I have executed the SP in SQL SMS and here are the two results, one for Insert and one for Update.

(0 row(s) affected)

(1 row(s) affected)
Return Value
------------
2

(1 row(s) affected)

(1 row(s) affected)
Return Value
------------
1

(1 row(s) affected)

As you can see, SQL seems to be doing everything correctly. Any ideas as to why when it gets called by C#, it always gets 0?

 

[Skydiver]

See:

Calling stored procedure with return value

I am trying to call a stored procedure from my C# windows application. The stored procedure is running on a local instance of SQL Server 2008. I am able to call the stored procedure but I am not ab...

stackoverflow.com

 

[tim8w]

See:

Calling stored procedure with return value

I am trying to call a stored procedure from my C# windows application. The stored procedure is running on a local instance of SQL Server 2008. I am able to call the stored procedure but I am not ab...

stackoverflow.com

Thanks. I changed to using a ReturnValue in the C# code and that solved the problem.

mySQLParam = myCmd.Parameters.Add("@Inserted", SqlDbType.Int);
myCmd.Parameters["@Inserted"].Direction = ParameterDirection.ReturnValue;
Int32 iInserted = 0;
myCmd.ExecuteNonQuery();
iInserted = (int) myCmd.Parameters["@Inserted"].Value;