[FIXED] How to prevent SQL rounding decimal(18,4) output in c# side

Issue

I make calculation in stored procedure and pass output parameter. It works fine execution on the SQL side.

But C# retrieves output value of type decimal(18,4) by automatically rounding if it is lower than 0,5 to 0, if higher than 0.5 to 1.

I need decimal(18,4) output precisely.

How can I prevent decimal(18,4) output value from being rounded?

C#

double sonuc=0;

SqlCommand cmd = new SqlCommand("findHBK", Connection.Con);
cmd.CommandType = CommandType.StoredProcedure;            

cmd.Parameters.AddWithValue("@yaFark", yaFark);
cmd.Parameters.AddWithValue("@yaTaban", yaTaban);

cmd.Parameters.Add("@sonuc", SqlDbType.Decimal).Direction = ParameterDirection.Output;               

if (cmd.Connection.State != ConnectionState.Open)
{
    cmd.Connection.Open();
}

cmd.ExecuteNonQuery();

sonuc = Convert.ToDouble(cmd.Parameters["@sonuc"].Value);

SQL (part of a stored procedure)

ALTER PROCEDURE [dbo].[findHBK]
    (@yaTaban DECIMAL(18, 2),
     @yaFark DECIMAL(18, 2),
     @sonuc DECIMAL(18, 4) OUTPUT)
AS
BEGIN 
    DECLARE @minhbk DECIMAL(18, 4), 
            @maxhbk DECIMAL(18, 4), 
            @hbkFark DECIMAL(18, 4)

    IF (@yaTaban BETWEEN 2000 AND 5000)
    BEGIN
        SET @maxhbk = (SELECT MAX(HBK) FROM TBL_MDHB 
                       WHERE YapiAlani BETWEEN @yaTaban AND @yaTaban + 200)
        SET @minhbk = (SELECT MIN(HBK) FROM TBL_MDHB
                       WHERE YapiAlani BETWEEN @yaTaban AND @yaTaban + 200)
        -- SET @sonuc = @maxhbk - (((@maxhbk - @minhbk) * @yaFark) / 200);
        SET @sonuc = 0.3292

        SELECT @sonuc
    END 
    SELECT @sonuc OUT
END

Solution

Please refer below code .

Please set Precision and Scale property of SqlParameter which you need to return . Its will be working perfectly .

        cmd.Parameters["@sonuc"].Precision = 18;
        cmd.Parameters["@sonuc"].Scale = 4;

Set this property after you add parameter in SqlCommand.

It will be helpful for you .

Thanks .

Answered By - Ronak Patel

Answer Checked By - Katrina (PHPFixing Volunteer)