21

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tony D.
  • 551
  • 1
  • 10
  • 26
  • Why do you need extra precision if the parameter value is 0? – The_Black_Smurf Jul 13 '15 at 19:37
  • 1
    Precision value dictates how many digits after the decimal you have, correct? Currently it's 0 just as a placeholder for this question. I"m just curious how to set (18,2) for the decimal size from the code. – Tony D. Jul 13 '15 at 19:40
  • You can either round it in C# using `Math.Round` or take the value as is and round in your SQL statement. – The_Black_Smurf Jul 13 '15 at 19:43
  • 1
    See: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.precision(v=vs.110).aspx – NoChance Jul 13 '15 at 19:48
  • In respect to @NoChance comment above, the relevant part is: _**You do not need to specify values for the Precision and Scale properties for input parameters, as they can be inferred from the parameter value.**_ – qxotk Jun 12 '19 at 21:35

3 Answers3

34

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"));
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • 1
    No need to find the parameter after adding it, `SqlParameterCollection.Add` returns the newly created parameter. `SqlParameter param = SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");` – pinkfloydx33 May 27 '18 at 20:39
  • Do I need to set `Precision` and `Scale` property prior to assign the value to the `SqlParameter` ? – hiFI Dec 11 '18 at 11:04
  • 1
    @hiFI No - just before executing the query. – D Stanley Dec 11 '18 at 14:08
  • 2
    Per Microsoft Documentation (See comments in original post) - you do not need to set the scale and precision on input parameters. – qxotk Jun 12 '19 at 21:38
  • is there any way to do a one liner for this solution? – paraJdox1 Mar 22 '21 at 08:58
  • @Hacki Added an example. – D Stanley Mar 22 '21 at 13:14
  • ohh, we can't do it something like this: SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, precision, scale, "myValue"); right? – paraJdox1 Mar 23 '21 at 08:04
  • @Hacki Yes, you can because the parameters to the constructor line up that way - I was just being explicit for all parameters and not relying on parameter order. – D Stanley Mar 23 '21 at 12:55
0
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;
        }
Диитрий С
  • 1
0

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').

Nyuno
  • 463
  • 4
  • 16