Stored procedure output parameter returning rounded value

Question

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);

Can you also post the C# code that is calling this procedure? — Jagmag, Oct 05 '12 at 05:21

score 14 · Accepted Answer · answered Oct 05 '12 at 05:26

14

You need to set up the C# parameter as

output -- obviously you've done this
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.

answered Oct 05 '12 at 05:26

RichardTheKiwi

105,798
26
196
262

This works for me thanks. But can we place the `Precision ` and `Scale ` in the source line?? like `SqlDbType.Decimal,19.3);` something like this ??? – Krishna Thota Oct 05 '12 at 05:33
You'd need this version of the SqlParameter constructor: http://msdn.microsoft.com/en-us/library/5a10hy4y.aspx Just follow the C# example on the page – RichardTheKiwi Oct 05 '12 at 07:07

score 0 · Answer 2 · answered Oct 05 '12 at 05:25

0

According to Microsoft decimal(p,s) should work for you. The money and smallmoneyt types are just a subset of decimal with precision of 4 places. So i think your problem comes from the type of the variable that is bound to the OUT parameter in C#.

answered Oct 05 '12 at 05:25

Desislav Kamenov

1,193
6
13

i'm using decimal to set as outparameter. I also tried double but same problem persists – Krishna Thota Oct 05 '12 at 05:27

Stored procedure output parameter returning rounded value

2 Answers2