1

When executing a stored procedure through C#, the value stored in the table does not change.

But when the stored procedure is executed through SQL Server Management Studio, it is executed correctly.

This is my C# code:

DatabaseLibrary.ParametersClear();

DatabaseLibrary.command.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier)).Value = formGuid;
DatabaseLibrary.command.Parameters.Add(new SqlParameter("@val", SqlDbType.Float)).Value = (Sumsrc.Net * Convert.ToDouble(ParityTxt.Text));
DatabaseLibrary.command.Parameters.Add(new SqlParameter("@in", SqlDbType.Bit)).Value = input ? 1 : 0;
DatabaseLibrary.command.Parameters.Add(new SqlParameter("@operation", SqlDbType.Bit)).Value = 1;

DatabaseLibrary.ExecuteStoredProcedure(StoredProcedure.AccountUpdate);

Stored procedure code:

CREATE PROCEDURE [dbo].[AccountUpdate]
    @guid uniqueidentifier,
    @val float,
    @in bit, -- 1 or 0
    @operation bit  -- 1 or 0
AS
BEGIN
    DECLARE @oldVal float

    IF (@in > 0)
    BEGIN
        IF (@operation > 0)
        BEGIN
            SET @oldVal = (SELECT ISNULL(Debit, 0) FROM ac00 WHERE GUID = @guid)

            UPDATE ac00  
            SET [Debit] = (@oldVal + @val) 
            WHERE GUID = @guid
        END
        ELSE
        BEGIN
            SET @oldVal = (SELECT ISNULL(Debit, 0) FROM ac00 WHERE guid = @guid)

            UPDATE ac00  
            SET [Debit] = (@oldVal - @val) 
            WHERE GUID = @guid
        END
    END
    ELSE
    BEGIN
        IF (@operation > 0)
        BEGIN
            SET @oldVal = (SELECT ISNULL(Credit, 0) FROM ac00 WHERE GUID = @guid)

            UPDATE ac00  
            SET [Credit] = (@oldVal + @val) 
            WHERE GUID = @guid
        END
        ELSE
        BEGIN
            SET @oldVal = (SELECT ISNULL(Credit, 0) FROM ac00 WHERE GUID = @guid)

            UPDATE ac00  
            SET [Credit] = (@oldVal - @val) 
            WHERE GUID = @guid
        END
    END
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Davied
  • 11
  • 2
  • Which dbms are you using? (The above code is product specific.) – jarlh May 09 '23 at 09:04
  • @jarlh SQL Server 2012 – Davied May 09 '23 at 09:08
  • @Davied Why are you using an obsolete, outdated version of SQL Server? – Dai May 09 '23 at 09:28
  • 3
    Sounds like a missing commit. – user743414 May 09 '23 at 09:28
  • 4
    `SqlDbType.Float)).Value = (Sumsrc.Net * Convert.ToDouble(ParityTxt.Text));` <-- Uh-oh, this looks like someone's being _naughty_ by incorrectly using IEEE-754 imprecise floating value types to represent currency or monetary values when they should be using `decimal(n,4)` or even the `money` type. I feel the legal punishment for this offence should involve being forced to write a program [that calculates](https://stackoverflow.com/q/3793838/159145) `16,777,216 + 1` using _only_ single-precision floats :) – Dai May 09 '23 at 09:30
  • @Dai `money` is actually a poor choice of a data type for *money* as well. it has some "quirks" shall we say. – Thom A May 09 '23 at 09:38
  • @ThomA I'm aware - but it's still better than T-SQL's `float`, _even if_ someone really wants to perform meaningless multiplication ops on currency values. – Dai May 09 '23 at 09:40
  • Please show more of the C# code. `ExecuteStoredProcedure` is not a function we are familiar with. By the way you can simplify your proc massively `UPDATE ac00 SET Debit = ISNULL(Debit, 0) + @val * IIF(@operation, 1, -1) WHERE GUID = @guid;` and again for `Credit` – Charlieface May 09 '23 at 10:33
  • `DatabaseLibrary` looks awfully like a shared instance. Is it thread-safe? – AlwaysLearning May 09 '23 at 11:01
  • Or even `ISNULL(Debit, 0) + @val * (CAST(@operation AS int) * 2 - 1)` – Charlieface May 09 '23 at 13:34
  • Did you test you function from within the SQL Studio? To be sure your T-SQL is working fine. – nabuchodonossor May 09 '23 at 14:08
  • @nabuchodonossor yes,it is working fine – Davied May 10 '23 at 03:02
  • @Davied: In the DatabaseLibrary (I don´t know this lib), is there an equivalent for .... CommandType = CommandType.StoredProcedure like in the "standard" methods used in .NET? – nabuchodonossor May 10 '23 at 17:00
  • In the absence of any other information I'd be casting suspicion on the `DatabaseLibrary` class/instance/singleton. Why are you using it instead of directly using `SqlConnection`, `SqlCommand` and `SqlParameters`? What does the source code of its `ParametersClear` and `ExecuteStoredProcedure` methods look like? I asked about it being thread-safe because there's a possibility of another thread interfering with your request in the time between the time the invocations of `ParametersClear` and `ExecuteStoredProcedure` methods. – AlwaysLearning May 11 '23 at 05:58

0 Answers0