Using Stored Procedures to insert into data column using Precision and Scale

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
2.0000

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);

Happy days!

Subscribe

0 comments