March 17, 2009 at 8:28 pm
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.
March 17, 2009 at 9:34 pm
Hi,
Show the coding in the SP
ARUN SAS
March 18, 2009 at 7:19 am
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
March 18, 2009 at 7:33 am
Are you dealing with INTs or it was just an example.
I am assuming it was just an example and that you are going to be using DECIMAL or something of this nature.
One way to do what you need is by casting your data to lower precision and then comparing the values.
You specify the input parameter with just one more precision you need in the database.
CREATE PROC insertinttotable
@MyValue DECIMAL(7,3) -- but you really want DECIMAL(7,2)
AS
DECLARE @TestValue DECIMAL(7,2)
SET @TestValue = @MyValue
IF @TestValue <> @MyValue
BEGIN
RAISERROR ......
RETURN
END
DO your insert here
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 7:53 pm
That's what I ended up doing. It is a pain to do it for the hundreds of columns. My work is never done!
March 18, 2009 at 9:25 pm
Hi,
Create the trigger for you requirement, when inserting the data it’s validate the each data type
ARUN SAS
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply