CODEWITHSUNDEEP
c#sqlparameter
Tony D.
Tony D.

Reputation: 560

SQLCommand.Parameters.Add - How to give decimal value size?

How would you specify this:

Decimal(18,2)

In this: 

SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");

Currently I have defined precision = 2 from the design side properties. I'm just curious as to how to accomplish this from the code. Thanks

Upvotes: 21

Views: 22665

Answers (3)

Nyuno
Nyuno

Reputation: 473

My answer is not directly connected with the OP's question, but I've seen a lot of people asking "why set the precision since it is taken from the value".

It has to do with the way SQL Server works when comparing the decimal parameter with the text column. Imagine you have column named strNumberColumn witch is of nvarchar type. If you define a @var Decimal(1,0) = '1', comparison on the condition where strNumberColumn >= @var will work only as long, as the longest entry in that column is between "0" and "9". If any of the entries go beyond, for example "10" or "123" you will get an OverflowException while converting string to decimal. What is important, that conversion is made "behind the scenes".

Please, do not bring arguments like "if that column should contain numbers it should not be made nvarchar" - I totally agree, but that is beyond the scope of the problem (sometimes you work with a legacy system and you have no influence over the column datatype). The above example shows a real life scenario when defining precision is required in order for the query to run successfully despite having a smaller precision amount assigned to the variable (like @var Decimal(12,2) = '1.0').

Upvotes: 1

Диитрий С
Диитрий С

Reputation: 1

var cmd = new SqlCommand()
SetDecimalParameter(cmd.Parameters.Add("@paramName", SqlDbType.Decimal), 18, 2).Value = 12.34;

SqlParameter SetDecimalParameter(SqlParameter parameter, byte precision, byte scale) {
            parameter.Precision = precision;
            parameter.Scale = scale;
            return parameter;
        }

Upvotes: 0

D Stanley
D Stanley

Reputation: 152596

There's not an overload of Add that lets you set the decimal precision inline, so you either need to create a SQlParameter object and add it to the collection:

SqlParameter param = new SqlParameter("@myValue", SqlDbType.Decimal);
param.SourceColumn = "myValue";
param.Precision = 18;
param.Scale = 2;
SqlComm.Parameters.Add(param);

or keep a reference to the parameter after adding it:

SqlParameter param = SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");
param.Precision = 18;
param.Scale = 2;

or using the parameter constructor:

SqlComm.Parameters.Add(new SqlParameter(
    parameterName = "@myValue", 
    dbType = SqlDbType.Decimal,
    precision = 18,
    scale = 2,
    sourceColumn = "myValue"));

Upvotes: 35

Related Questions