Cause a Loss of Precision to Fail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cause a Loss of Precision to Fail

Author

Topic

PaulTeal
Yak Posting Veteran

67 Posts

Posted - 2009-03-17 : 22:30:48

I am writing a stored procedure that inserts data into a table. The data may change over time and I want to ensure that the procedure will fail at a later date if there is a loss of precision during the insert. Inserting "SET ARITHABORT ON" into the store procedure does not appear to cause the results I want.

Paul Teal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-17 : 23:05:56

Are you saying that if the column is say: numeric(10,4) and someone tries to insert 12.432567 you want an error to occur because the value would be converted to 12.4326 losing precision?

If the input parameter of the SP is the same datatype and precision as the column then you won't be able to do this from within the SP. Can you perform this validation on the frontend?

Perhaps if you make the input parameter float(53) you can check with something like: if @floatInput != convert(numeric(10,4),@floatInput) RAISERROR(...

Be One with the Optimizer
TG

PaulTeal
Yak Posting Veteran

67 Posts

Posted - 2009-03-18 : 09:24:45

That is exactly what I want to happen. My problem is that the data source does could change precision without letting me know. The data store is quite large and I want to use the smallest datatype I can. My plan is that if the insert could fail, then I can only switch to a larger datatype when I need to. Here is a script that shows what I want to happen:

CREATE TABLE dbo.MyTable (
MyColumn INT)

-- I want this to fail, rather than insert and loose precision:
INSERT INTO dbo.MyTable
SELECT
1.5 AS MyColumn

SELECT * FROM dbo.MyTable

DROP TABLE dbo.MyTable

I was hoping for some sort of global setting, but I could check each column as you demonstrate.

Paul Teal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-18 : 11:06:56

Try using this:

SET NUMERIC_ROUNDABORT ON

It works for fixed precision columns - but does not seem to work on INT columns.


SET NUMERIC_ROUNDABORT OFF
select convert(numeric(5,2), 1.1234)

SET NUMERIC_ROUNDABORT ON
select convert(numeric(5,2), 1.1234)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources