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

Question

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

Wrecker

Why do you need extra precision if the parameter value is 0? – Pagel

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. – Wrecker

You can either round it in C# using Math.Round or take the value as is and round in your SQL statement. – Pagel

See: msdn.microsoft.com/en-us/library/… – Guanaco

In respect to @Guanaco 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. – Authorize

Answer 1

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"));

Continuation

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"); – Accumulator

Do I need to set Precision and Scale property prior to assign the value to the SqlParameter ? – Runty

@Runty No - just before executing the query. – Continuation

Per Microsoft Documentation (See comments in original post) - you do not need to set the scale and precision on input parameters. – Authorize

is there any way to do a one liner for this solution? – Hobgoblin

@Hacki Added an example. – Continuation

ohh, we can't do it something like this: SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, precision, scale, "myValue"); right? – Hobgoblin

@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. – Continuation

Answer 2

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;
        }

Housefather

Answer 3

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

Anosmia