CODEWITHSUNDEEP
c#sql
kashif
kashif

Reputation: 3834

How to use SQL Server stored procedure numeric parameter in c#

I'm using the following SQL Server stored procedure in one of my C# projects.

create procedure [dbo].[TestRecordSelect]
@OMarks numeric(3,1) output
as 
begin
select @OMarks = isnull(sum(cast(OMarks as numeric(3,1))),0) from Marks 
end

and I'm getting the desired result i.e. 24.9 when executing the procedure in SQL Server. But when I'm using the same procedure in my C# project and calling the @OMarks as follows

cmd.Parameters.Add("@OMarks", SqlDbType.Decimal).Direction = ParameterDirection.Output;
tbomarks.Text = cmd.Parameters["@OMarks"].Value.ToString();

I'm getting 25 instead of 24.9 that is the rounded up data.

My problem is I don't want to get 25. I want to get 24.9 in tbomarks

Upvotes: 4

Views: 4622

Answers (2)

zimdanen
zimdanen

Reputation: 5626

SqlParameter param = new SqlParameter("@OMarks", SqlDbType.Decimal);
param.Direction = ParameterDirection.Output;
param.Precision = 3;
param.Scale = 1;
cmd.Parameters.Add(param);

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.scale.aspx

Upvotes: 7

MartW
MartW

Reputation: 12538

In your C#, you haven't specified the Scale (or Precision) of your parameter, which means you get zero decimal places.

Data may be truncated if the Scale property is not explicitly specified and the data on the server does not fit in scale 0 (the default)

Precision and Scale are required for output parameters

Upvotes: 5

Related Questions