What is rounding my values in DECIMAL columns?











up vote
1
down vote

favorite












I have a SQL update request. I want it to only modify the columns for which a value is supplied in the update model, so I use the general form myCol = ISNULL(@myParam, myCol). Here is the full SQL...



update Justif set 
DateTransaction = ISNULL(@dateTransaction,DateTransaction),
Cif = ISNULL(@cif,Cif),
NomFournisseur = ISNULL(@nomFournisseur,NomFournisseur),
MontantHT = ISNULL(@montantHT,MontantHT),
MontantTtc = ISNULL(@montantTtc,MontantTtc),
TauxTva = ISNULL(@tauxTva,TauxTva),
MontantTva = ISNULL(@montantTva,MontantTva),
ReceptionNumber = ISNULL(@receptionNumber,ReceptionNumber),
Locked = IIF(@locked > 0,GETDATE(),null),
Used = IIF(@used is not null, @used, Used),
NatureOcr = ISNULL(@natureOcr, NatureOcr)
where JustifID = @justifId


Now, the weirdest thing, at one point the app uses this request just to set the column Used.



The montantTtc parameter, like all the others, is initialized with DBNull.Value (and the SqlDbType set to decimal), then to my great surprise, the decimal columns are rounded to the nearest int.



What am I not understanding about ISNULL()?



enter image description here










share|improve this question




















  • 3




    The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
    – Ben
    Nov 22 at 9:36












  • @Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
    – bbsimonbb
    Nov 22 at 9:38






  • 1




    The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
    – Jeroen Mostert
    Nov 22 at 9:48








  • 1




    @JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
    – bbsimonbb
    Nov 22 at 9:52






  • 1




    A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
    – Jeroen Mostert
    Nov 22 at 10:31















up vote
1
down vote

favorite












I have a SQL update request. I want it to only modify the columns for which a value is supplied in the update model, so I use the general form myCol = ISNULL(@myParam, myCol). Here is the full SQL...



update Justif set 
DateTransaction = ISNULL(@dateTransaction,DateTransaction),
Cif = ISNULL(@cif,Cif),
NomFournisseur = ISNULL(@nomFournisseur,NomFournisseur),
MontantHT = ISNULL(@montantHT,MontantHT),
MontantTtc = ISNULL(@montantTtc,MontantTtc),
TauxTva = ISNULL(@tauxTva,TauxTva),
MontantTva = ISNULL(@montantTva,MontantTva),
ReceptionNumber = ISNULL(@receptionNumber,ReceptionNumber),
Locked = IIF(@locked > 0,GETDATE(),null),
Used = IIF(@used is not null, @used, Used),
NatureOcr = ISNULL(@natureOcr, NatureOcr)
where JustifID = @justifId


Now, the weirdest thing, at one point the app uses this request just to set the column Used.



The montantTtc parameter, like all the others, is initialized with DBNull.Value (and the SqlDbType set to decimal), then to my great surprise, the decimal columns are rounded to the nearest int.



What am I not understanding about ISNULL()?



enter image description here










share|improve this question




















  • 3




    The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
    – Ben
    Nov 22 at 9:36












  • @Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
    – bbsimonbb
    Nov 22 at 9:38






  • 1




    The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
    – Jeroen Mostert
    Nov 22 at 9:48








  • 1




    @JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
    – bbsimonbb
    Nov 22 at 9:52






  • 1




    A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
    – Jeroen Mostert
    Nov 22 at 10:31













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I have a SQL update request. I want it to only modify the columns for which a value is supplied in the update model, so I use the general form myCol = ISNULL(@myParam, myCol). Here is the full SQL...



update Justif set 
DateTransaction = ISNULL(@dateTransaction,DateTransaction),
Cif = ISNULL(@cif,Cif),
NomFournisseur = ISNULL(@nomFournisseur,NomFournisseur),
MontantHT = ISNULL(@montantHT,MontantHT),
MontantTtc = ISNULL(@montantTtc,MontantTtc),
TauxTva = ISNULL(@tauxTva,TauxTva),
MontantTva = ISNULL(@montantTva,MontantTva),
ReceptionNumber = ISNULL(@receptionNumber,ReceptionNumber),
Locked = IIF(@locked > 0,GETDATE(),null),
Used = IIF(@used is not null, @used, Used),
NatureOcr = ISNULL(@natureOcr, NatureOcr)
where JustifID = @justifId


Now, the weirdest thing, at one point the app uses this request just to set the column Used.



The montantTtc parameter, like all the others, is initialized with DBNull.Value (and the SqlDbType set to decimal), then to my great surprise, the decimal columns are rounded to the nearest int.



What am I not understanding about ISNULL()?



enter image description here










share|improve this question















I have a SQL update request. I want it to only modify the columns for which a value is supplied in the update model, so I use the general form myCol = ISNULL(@myParam, myCol). Here is the full SQL...



update Justif set 
DateTransaction = ISNULL(@dateTransaction,DateTransaction),
Cif = ISNULL(@cif,Cif),
NomFournisseur = ISNULL(@nomFournisseur,NomFournisseur),
MontantHT = ISNULL(@montantHT,MontantHT),
MontantTtc = ISNULL(@montantTtc,MontantTtc),
TauxTva = ISNULL(@tauxTva,TauxTva),
MontantTva = ISNULL(@montantTva,MontantTva),
ReceptionNumber = ISNULL(@receptionNumber,ReceptionNumber),
Locked = IIF(@locked > 0,GETDATE(),null),
Used = IIF(@used is not null, @used, Used),
NatureOcr = ISNULL(@natureOcr, NatureOcr)
where JustifID = @justifId


Now, the weirdest thing, at one point the app uses this request just to set the column Used.



The montantTtc parameter, like all the others, is initialized with DBNull.Value (and the SqlDbType set to decimal), then to my great surprise, the decimal columns are rounded to the nearest int.



What am I not understanding about ISNULL()?



enter image description here







sql-server ado.net






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 11:18









Jeroen Mostert

17.1k2149




17.1k2149










asked Nov 22 at 9:33









bbsimonbb

8,79663245




8,79663245








  • 3




    The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
    – Ben
    Nov 22 at 9:36












  • @Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
    – bbsimonbb
    Nov 22 at 9:38






  • 1




    The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
    – Jeroen Mostert
    Nov 22 at 9:48








  • 1




    @JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
    – bbsimonbb
    Nov 22 at 9:52






  • 1




    A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
    – Jeroen Mostert
    Nov 22 at 10:31














  • 3




    The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
    – Ben
    Nov 22 at 9:36












  • @Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
    – bbsimonbb
    Nov 22 at 9:38






  • 1




    The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
    – Jeroen Mostert
    Nov 22 at 9:48








  • 1




    @JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
    – bbsimonbb
    Nov 22 at 9:52






  • 1




    A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
    – Jeroen Mostert
    Nov 22 at 10:31








3




3




The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
– Ben
Nov 22 at 9:36






The type of the expression is the type of the first parameter. So if @montantTtc is integer, then ISNULL(@montantTtc, MontantTtc) will also be integer. Likely this is the cause of the rounding.
– Ben
Nov 22 at 9:36














@Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
– bbsimonbb
Nov 22 at 9:38




@Ben so I need to do an explicit cast of @montantTtc? When it's DBNull, it's assuming integer?
– bbsimonbb
Nov 22 at 9:38




1




1




The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
– Jeroen Mostert
Nov 22 at 9:48






The Scale and Precision of your parameter are both 0. I don't know what ADO.NET does in this case. Normally it "assumes proper defaults" based on the value, but the value is NULL -- I wouldn't be surprised if this gets you DECIMAL(38, 0) or some such nonsense, which is then used in a conversion in the IIF. You can check on the T-SQL side with SQL_VARIANT_PROPERTY(@param, 'Scale'). Even better would be to actually type the parameter correctly and not leave it up to any inference.
– Jeroen Mostert
Nov 22 at 9:48






1




1




@JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
– bbsimonbb
Nov 22 at 9:52




@JeroenMostert this is something I often want to do, and really want to get right. Is there a better construction?
– bbsimonbb
Nov 22 at 9:52




1




1




A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
– Jeroen Mostert
Nov 22 at 10:31




A test with SQL Profiler suggests that ADO.NET passes a DECIMAL parameter that has no assigned precision and scale as DECIMAL(29, 0). That would explain your results. (Why DECIMAL(29, 0)? Because Decimal.MaxValue has 29 digits.) The solution is to properly set Scale, Precision and Length for all parameters, always, even when passing NULL. In other words, avoid generic code that doesn't -- one size does not fit all.
– Jeroen Mostert
Nov 22 at 10:31












1 Answer
1






active

oldest

votes

















up vote
2
down vote



accepted










Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:



using (var connection = new SqlConnection(@"Data Source=(localdb)MSSQLLocalDB")) {
connection.Open();
using (var command = new SqlCommand(@"
DECLARE @v DECIMAL(4,1) = 123.4;
SELECT
ISNULL(@p, @v),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
)) {
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
using (var reader = command.ExecuteReader()) {
reader.Read();
Console.WriteLine($@"
Munged value: {reader.GetValue(0)}
Precision: {reader.GetValue(1)}
Scale: {reader.GetValue(2)}
");
}
}
}



Munged value: 123

Precision: 29

Scale: 0




The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use



COALESCE(@p, @v)


which is equivalent to an expression of the form



CASE WHEN @p IS NOT NULL THEN @p ELSE @v END


Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.






share|improve this answer























  • or instead of CASE: COALESCE(@p, @v)
    – Moe Sisko
    Nov 26 at 3:17










  • @MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
    – Jeroen Mostert
    Nov 26 at 7:59











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427771%2fwhat-is-rounding-my-values-in-decimal-columns%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
2
down vote



accepted










Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:



using (var connection = new SqlConnection(@"Data Source=(localdb)MSSQLLocalDB")) {
connection.Open();
using (var command = new SqlCommand(@"
DECLARE @v DECIMAL(4,1) = 123.4;
SELECT
ISNULL(@p, @v),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
)) {
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
using (var reader = command.ExecuteReader()) {
reader.Read();
Console.WriteLine($@"
Munged value: {reader.GetValue(0)}
Precision: {reader.GetValue(1)}
Scale: {reader.GetValue(2)}
");
}
}
}



Munged value: 123

Precision: 29

Scale: 0




The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use



COALESCE(@p, @v)


which is equivalent to an expression of the form



CASE WHEN @p IS NOT NULL THEN @p ELSE @v END


Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.






share|improve this answer























  • or instead of CASE: COALESCE(@p, @v)
    – Moe Sisko
    Nov 26 at 3:17










  • @MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
    – Jeroen Mostert
    Nov 26 at 7:59















up vote
2
down vote



accepted










Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:



using (var connection = new SqlConnection(@"Data Source=(localdb)MSSQLLocalDB")) {
connection.Open();
using (var command = new SqlCommand(@"
DECLARE @v DECIMAL(4,1) = 123.4;
SELECT
ISNULL(@p, @v),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
)) {
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
using (var reader = command.ExecuteReader()) {
reader.Read();
Console.WriteLine($@"
Munged value: {reader.GetValue(0)}
Precision: {reader.GetValue(1)}
Scale: {reader.GetValue(2)}
");
}
}
}



Munged value: 123

Precision: 29

Scale: 0




The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use



COALESCE(@p, @v)


which is equivalent to an expression of the form



CASE WHEN @p IS NOT NULL THEN @p ELSE @v END


Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.






share|improve this answer























  • or instead of CASE: COALESCE(@p, @v)
    – Moe Sisko
    Nov 26 at 3:17










  • @MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
    – Jeroen Mostert
    Nov 26 at 7:59













up vote
2
down vote



accepted







up vote
2
down vote



accepted






Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:



using (var connection = new SqlConnection(@"Data Source=(localdb)MSSQLLocalDB")) {
connection.Open();
using (var command = new SqlCommand(@"
DECLARE @v DECIMAL(4,1) = 123.4;
SELECT
ISNULL(@p, @v),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
)) {
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
using (var reader = command.ExecuteReader()) {
reader.Read();
Console.WriteLine($@"
Munged value: {reader.GetValue(0)}
Precision: {reader.GetValue(1)}
Scale: {reader.GetValue(2)}
");
}
}
}



Munged value: 123

Precision: 29

Scale: 0




The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use



COALESCE(@p, @v)


which is equivalent to an expression of the form



CASE WHEN @p IS NOT NULL THEN @p ELSE @v END


Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.






share|improve this answer














Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:



using (var connection = new SqlConnection(@"Data Source=(localdb)MSSQLLocalDB")) {
connection.Open();
using (var command = new SqlCommand(@"
DECLARE @v DECIMAL(4,1) = 123.4;
SELECT
ISNULL(@p, @v),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'),
SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
)) {
command.Connection = connection;
command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
using (var reader = command.ExecuteReader()) {
reader.Read();
Console.WriteLine($@"
Munged value: {reader.GetValue(0)}
Precision: {reader.GetValue(1)}
Scale: {reader.GetValue(2)}
");
}
}
}



Munged value: 123

Precision: 29

Scale: 0




The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use



COALESCE(@p, @v)


which is equivalent to an expression of the form



CASE WHEN @p IS NOT NULL THEN @p ELSE @v END


Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 26 at 7:58

























answered Nov 22 at 11:08









Jeroen Mostert

17.1k2149




17.1k2149












  • or instead of CASE: COALESCE(@p, @v)
    – Moe Sisko
    Nov 26 at 3:17










  • @MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
    – Jeroen Mostert
    Nov 26 at 7:59


















  • or instead of CASE: COALESCE(@p, @v)
    – Moe Sisko
    Nov 26 at 3:17










  • @MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
    – Jeroen Mostert
    Nov 26 at 7:59
















or instead of CASE: COALESCE(@p, @v)
– Moe Sisko
Nov 26 at 3:17




or instead of CASE: COALESCE(@p, @v)
– Moe Sisko
Nov 26 at 3:17












@MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
– Jeroen Mostert
Nov 26 at 7:59




@MoeSisko: thanks, added. It's shorter than CASE (but still uses the same rules for promotion, which contradicts or at least is not clearly explained by the documentation stating that COALESCE "returns the data type of expression with the highest data type precedence").
– Jeroen Mostert
Nov 26 at 7:59


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427771%2fwhat-is-rounding-my-values-in-decimal-columns%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

Calculate evaluation metrics using cross_val_predict sklearn

Insert data from modal to MySQL (multiple modal on website)