CodePlexProject Hosting for Open Source Software

2

Closed

Made a simple table with decimal columns all with precision 6 and scale varying from 1 to 5 (2 sets, one nullable, one not)

CREATE TABLE [dbo].[DecimalTestingTable](

Reverse engineer didn't include any information about the precision or scale.

I made a console app that just tried to insert pi (with required cast to decimal) into those columns.

exec sp_executesql N'insert [dbo].[DecimalTestingTable]([NN1], [NN2], [NN3], [NN4], [NN5], [Dec1], [Dec2], [Dec3], [Dec4], [Dec5])

values (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9)

',N'@0 decimal(18,2),@1 decimal(18,2),@2 decimal(18,2),@3 decimal(18,2),@4 decimal(18,2),@5 decimal(18,2),@6 decimal(18,2),@7 decimal(18,2),@8 decimal(18,2),@9 decimal(18,2)',@0=3.14,@1=3.14,@2=3.14,@3=3.14,@4=3.14,@5=3.14,@6=3.14,@7=3.14,@8=3.14,@9=3.14

CREATE TABLE [dbo].[DecimalTestingTable](

```
[Dec1] [decimal](6, 1) NULL,
[Dec2] [decimal](6, 2) NULL,
[Dec3] [decimal](6, 3) NULL,
[Dec4] [decimal](6, 4) NULL,
[Dec5] [decimal](6, 5) NULL,
[NN1] [decimal](6, 1) NOT NULL,
[NN2] [decimal](6, 2) NOT NULL,
[NN3] [decimal](6, 3) NOT NULL,
[NN4] [decimal](6, 4) NOT NULL,
[NN5] [decimal](6, 5) NOT NULL
```

) Reverse engineer didn't include any information about the precision or scale.

I made a console app that just tried to insert pi (with required cast to decimal) into those columns.

```
var context = new DecimalTestingContext();
context.DecimalTestingTables.Add(new DecimalTestingTable()
{
Dec1 = (decimal) Math.PI,
Dec2 = (decimal) Math.PI,
Dec3 = (decimal) Math.PI,
Dec4 = (decimal) Math.PI,
Dec5 = (decimal) Math.PI,
NN1 = (decimal) Math.PI,
NN2 = (decimal) Math.PI,
NN3 = (decimal) Math.PI,
NN4 = (decimal) Math.PI,
NN5 = (decimal) Math.PI,
});
context.SaveChanges();
```

There's a separate issue (that might be By Design, although it's odd to me) where on the client side it truncates (not rounds) the values based on the scale in the target column, but for this issue, since the precision and scale weren't included in the generated
code, they were all treated as default decimal (which is decimal (18,2)), resulting in this generated SQL seen via SQL Profiler:
exec sp_executesql N'insert [dbo].[DecimalTestingTable]([NN1], [NN2], [NN3], [NN4], [NN5], [Dec1], [Dec2], [Dec3], [Dec4], [Dec5])

values (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9)

',N'@0 decimal(18,2),@1 decimal(18,2),@2 decimal(18,2),@3 decimal(18,2),@4 decimal(18,2),@5 decimal(18,2),@6 decimal(18,2),@7 decimal(18,2),@8 decimal(18,2),@9 decimal(18,2)',@0=3.14,@1=3.14,@2=3.14,@3=3.14,@4=3.14,@5=3.14,@6=3.14,@7=3.14,@8=3.14,@9=3.14

No files are attached

## comments

glennc wrote Dec 13, 2012 at 10:19 PM

BriceLambson wrote Dec 26, 2013 at 6:37 PM

BriceLambson wrote Dec 26, 2013 at 7:12 PM

allanhorwitz wrote Thu at 8:44 PM