linda22 - 11 months ago 55

SQL Question

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 Source

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 .