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);
You need to set up the C# parameter as
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.