I was trying to track down an error in one of our internal OLAP collection processes, when I noticed something very peculiar: An insert was failing, appeared to have the proper error trapping, but was not causing an error. How does that work?
Wouldnt the @@ERROR test the select @RC = @@Rowcount?
To the best of my knowledge the @@ERROR would be testing the last executed SQL statement. AFAIK the @@vars test the last statement so you have to add them directly after the statement you want to test.
Congratulations Chris (RocketScientist) and SQLTeam! This short article stirred enough interest to be linked to from SQLWire.com (http://www.sqlwire.com/brief.asp?1783) More exposure is a good thing!
Because I am trying to make an insert, but the inserted Data is not the datatype the table expects. It's easy to handle this error by changing the datatype of the column but I am trying to create this Table automatically... and I can not be sure that the data I am trying to insert is always the same and always in the proper format.
You can't "handle" a fatal error...just like you can't cure or heal a fatal injury. The only thing you can do is to take steps to prevent it happening (which is the best kind of error handling there is!) Regardless, it would be better for you to work at improving the quality of the data file you're importing. Get whoever is supplying it to you to clean it up.
Failing that, import the file into a generically formatted staging table. This table has no constraints, allow nulls, etc., and stores the data as varchar. Then you can apply CAST and CONVERT functions to change the data over to its proper data type and then INSERT it into the final destination table. This is a fairly common technique and is much faster and more reliable than trying to construct the "right" table on the fly.
Article: How to NOT trap errors in a stored procedure
Posted - 12/02/2001 : 17:38:15
.