SqlDataType.Decimal rounds to zero !?

[Guest]

Hi, I'm writing a module to insert records to the main accounts table in our
CRM database. I've noticed in testing that the tax percent, which is a
decimal data type with length 5, default value (0), precision 9 and scale 6
always enters zero.

I've noticed if I run the test T-SQL statement it always returns a value of
0.0 instead of 0.085.

Any ideas on what I'm missing would be hugely appreciated, I'm sort of
tearing my hair out here.

Thanks,

Andre

declare @taxpercent decimal(5)
set @taxpercent = 0.085000
select @taxpercent

 

[Stephany Young]

The length 0f 5 that you refer to is the numbers of bytes that SQL Server
uses to store the value in.

From a logical point of view the variable, requiring precision 9 and scale 6
must be declared as:

declare @taxpercent decimal(9,6)

decimal(5) is the same as decimal(5,0) which is precision 5 and scale 0 and
the result you are observing is completely correct.

 

[Guest]

Stephany,

Thanks, you explanation made a lot of sense.

I'd read this on BOL earlier but it hadn't clicked until I read your post.

Andre Ranieri