Error Handling in a stored procedure

I have an application where I am bcping a file into a holder table and then looping through each of the rows in that table to add it into the main table for the app. If the data is improperly formatted (ie someone accidently enters 39.Y6 instead of 39.66), we still want to keep it in the system, so we can update the bad fields manually and then import it into the system and keep going through the loop so that if there are 50 rows to import and only one of them is bad, the other 49 will still get imported fine.

I am putting each field from the holder table into a variable and passing them into an existing stored procedure that updates the main table. I had originally checked each one of those variables that had to be numeric or a date to make sure that it was correct before passing them into the procedure but there are about 30 fields so this made the application run unacceptably slow. I then just checked the @.@.Error value after passing them into the stored procedure (tried after the sp and after the INSERT statement inside the sp) to get that this row didn't insert correctly and move onto the next one. The problem is that the "Error converting data type varchar to numeric" doesn't seem to be handled by the error handling and just bombs the whole thing, so none of the subsequent rows or processing is done.

Is there any way to handle this error and continue the processing without the whole stored procedure crashing? The data entry is being outsourced to India (grrr...), so I don't have any control over checking the data when they enter it and have to do it from within my application on the database side.What you can probably do is in your insert stored procedure, set up an output parameter to send any error code back to the calling stored procedure. The error in your insert probably won't bubble up so do something like this

Create Procedure InsertIt
(
@.fields ...,
@.errorcode int = null output
) as
insert into...
set @.errorcode = @.@.error
return

the error will be returned back as an output parameter rather than you trying to use @.@.error.

I've done similar things in the past and what I did was run a query that searched for the possible error conditions and flagged those records as needing attention. Then just do an insert ... select query, selecting only unflagged records, into your main table. As long as you make sure data types are good to go, you shouldn't have a failure. Probably faster than using a cursor and checking each field with in each iteration. Of course, if you the insert is more complex than just a plain import, the cursor may be necessary. Just a thought.