Stored procedure output parameter returning rounded value

Krishna Thota picture Krishna Thota · Oct 5, 2012 · Viewed 9.6k times · Source

I'm using output parameters to return values from a stored procedure.

Declaration in stored procedure is : @GrandTtl DECIMAL(19,3) OUT

The SELECT query is:

SET @GrandTtl = (SELECT TOP 1 Bill_Amount 
                 FROM Tbl_Restaurant_Kitchen_Order_Bill_Details 
                 WHERE Bill_Number = @billno)

For example, the select query returns the value 4087.67 then the output parameter value is returned as 4088 from SQL Server to C#.

Here is the C# code calling the stored procedure:

SqlCommand cmd = new SqlCommand("Sp_RestCC_BillDetails", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter OutParam26 = cmd.Parameters.Add("@GrandTtl", SqlDbType.Decimal,19);

da = new SqlDataAdapter(cmd);

con.Open();
da.Fill(ds, "dtRestCC_Items");
con.Close();

objRCCBEL.GrandTtlOut = Convert.ToDecimal(cmd.Parameters["@GrandTtl"].Value);

Answer

RichardTheKiwi picture RichardTheKiwi · Oct 5, 2012

You need to set up the C# parameter as

  1. output -- obviously you've done this
  2. decimal type, with a correct/compatible scale

SqlParameter parm = new SqlParameter("@GrandTtl", SqlDbType.Decimal); 
parm.Precision = 19;
parm.Scale = 3;
parm.Direction = ParameterDirection.Output; 
cmd.Parameters.Add(parm);

If you do not set the scale, the default is 0. Ref: SqlParameter.Scale Property

The number of decimal places to which Value is resolved. The default is 0.