linda22 linda22 - 1 month ago 16
SQL Question

How to prevent Sql rounding decimal(18,4) output in c# side

I make calculation in stored procedure and pass output parameter.
It works fine execution in SQL side.
But c# retrieves output value -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 rounding?

c#

double sonuc=0;
SqlCommand cmd = new SqlCommand("findHBK", Connection.Con);
cmd.Parameters.AddWithValue("@yaFark", yaFark);
cmd.Parameters.AddWithValue("@yaTaban", yaTaban);
cmd.Parameters.Add("@sonuc", SqlDbType.Decimal).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
sonuc = Convert.ToDouble(cmd.Parameters["@sonuc"].Value);


sql (part of stored prodecure)

ALTER proc [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

Answer

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 .

Comments