SQL 2000 'TRY CATCH like' Error Handling

Question

This is a sql 2000 database that I am working with.

I have what I call a staging table that is a raw data dump of data, so everything is ntext or nvarchar(255).

I need to cast/convert all of this data into the appropriate data types (ie int, decimal, nvarchar, etc.)

The way I was going to do this was to iterate through all records using a while loop and attempt a CAST on each column on a single record during each iteration, after I visit a particular record I flag it as processed (bit field).

But how can I log the error when/if it occurs but allow the while loop to continue.

At first I implemented this using a TRY CATCH in a local SQL 2005 instance (to get the project going) and all was working well, but i learned today that the dev & production database that the international DBA's have set up is a SQL 2000 instance so I have to conform.

EDIT: I am using a SSIS package to populate the staging table. I see that now I must revisit that package and implement a script component to handle the conversions. Thanks guys

EDIT: I am doing this on a record by record basis, not a batch insert, so the transaction idea seems like it would be feasible but I'm not sure how to trap @@ERROR and allow the stored procedure to continue.

EDIT: I really like Guy's approach, I am going to implement it this way.

Answer 1

Generally I don't like "loop through the record" solutions as they tend to be slow and you end up writing a lot of custom code.

So...

Depending on how many records are in your staging table, you could post process the data with a series of SQL statements that test the columns for correctness and mark any records that fail the test.

i.e.

UPDATE staging_table
SET status_code = 'FAIL_TEST_1'
WHERE status_code IS NULL
AND ISDATE(ntext_column1) = 0;

UPDATE staging_table
SET status_code = 'FAIL_TEST_2'
WHERE status_code IS NULL
AND ISNUMERIC(ntext_column2) = 0;

etc...

Finally

INSERT INTO results_table ( mydate, myprice )
SELECT ntext_column1 AS mydate, ntext_column2 AS myprice
FROM staging_table
WHERE status_code IS NULL;

DELETE FROM staging_table
WHERE status_code IS NULL;

And the staging table has all the errors, that you can export and report out.