And another head-scratcher today. I wanted to insert some data into a table using a stored procedure (nothing new there). This particular table had a column using the numeric data type as defined below
Column1 NUMERIC(8, 4)
The stored procedure that I used already existed, so it took me a bit of debugging time to find that the stored procedure was at fault.
It was defined as follows
CREATE PROCEDURE MyStoredProcedure AS INSERT INTO MyTable (Column1 NUMERIC) VALUES (@Column1Value);
With the stored procedure defined like this, every execution of the stored procedure would cause the result to be rounded.
For example, lets say that the stored procedure like so
EXEC MyStoredProcedure 1.68
This would then return the result
Not a good place.
After a bit of trial and error and discussing with a colleague, we found that the precision and scale values must also be provided in the stored procedure.
So, the revised and correct stored procedure definition is as follows
CREATE PROCEDURE MyStoredProcedure AS INSERT INTO MyTable (Column1 NUMERIC(8,4)) VALUES (@Column1Value);