Search code examples
sqlsql-serverintdecimalsql-insert

DECIMAL values being converted to INT + 1 on its own

I have a C# app where the user fills a form to store a product in a SQL Server database.

The problem is that every time a product is stored (through the user filling a form), the price (a decimal) is automatically converted to int and has 1 added to it.

I initially thought it was an issue with the app, however, the registration process is pretty simple and I didn't find any error there, so I inserted a row directly from SQL Server and the issue presented itself, so this tells me the issue is in SQL Server, not in the app.

Executing

insert into product (code, description, unit_price, stock, category_code)
values (7, 'Window Cleaner', 20.50, 20, 3)

Results into price being 21.

This is the table definition

CODE INT NOT NULL,
DESCRIPTION VARCHAR(30) NOT NULL,
UNIT_PRICE DECIMAL NOT NULL,
STOCK INT NOT NULL,
CATEGORY_CODE INT NOT NULL
CONSTRAINT PK_PRODUCT PRIMARY KEY(CODE),
CONSTRAINT FK_CATEGORY_CODE FOREIGN KEY (CATEGORY_CODE) REFERENCES Category(CODE),
CONSTRAINT PRODUCT_POSITIVE_VALUES CHECK(UNIT_PRICE > 0 AND CODE >= 0 AND STOCK >= 0)
Solution

  • You have used the following to define your column:

    UNIT_PRICE DECIMAL NOT NULL

    This has no precision nor scale and will therefore use the default precision (18) and scale (0). The default scale of 0 is effectively an int. So when you insert/update a value the value will get rounded to an int. To solve your problem define your column with the correct precision and scale e.g.

    UNIT_PRICE DECIMAL(9,2) NOT NULL

    Reference