Trapping Data Conversion Errors

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 2000 Forums

SQL Server Development (2000)

Trapping Data Conversion Errors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 09:59:20

Andy writes "Is there anyway to trap for a data conversion error in code. Example:

Table 1
line_1 int,
line_2 varchar(40),
line_3 varchar(40),
line_4 varchar(40)

Then have a sproc as follows that tries to insert a varchar into an int datatype:

CREATE PROCEDURE [DBO].[TEST1] AS

BEGIN
declare @iError int
INSERT Table1
(LINE_1,
LINE_2,
LINE_3,
LINE_4)
VALUES
('tw',
'test',
'test',
'Y')
Select @iError=@@error
If @iError <> 0
Print 'Error'
END
GO

When the sproc is run using Exec TEST1 I get this:

Server: Msg 245, Level 16, State 1, Procedure TEST1, Line 17
Syntax error converting the varchar value 'tw' to a column of data type int.

I am trying to get the error code returned."

dsdeming

479 Posts

Posted - 2002-04-03 : 13:07:15

I don't know of any way to do what you're asking short of running ISNUMERIC, ISDATE, etc on the data before attemping the insert or update.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-03 : 13:25:30

Look SET ARITHIGNORE up in BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources