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

Question

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

zimdanen · Accepted Answer · 2012-04-24 18:14:38Z

7

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

answered Apr 24, 2012 at 18:14

zimdanen

5,6367 gold badges47 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kashif Over a year ago

thanks zimdanen now I'm getting the desired result 24.9 in otomarks. thanks again

MartW · Accepted Answer · 2012-04-24 18:11:28Z

5

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

answered Apr 24, 2012 at 18:11

MartW

12.6k3 gold badges46 silver badges68 bronze badges

1 Comment

MartW Over a year ago

I agree with you :) That'll teach me to get dinner straight after posting instead of improving my answer ;)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related