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.
| Author |
Topic |
|
GhantaBro
Posting Yak Master
215 Posts |
 Posted - 2009-07-10 : 00:45:38
|
| Hey Guys,I need some suggestion on this. I need to use files, stage them and create few reports. I have a logging table that has flags and a place holder to enter reasons for failures. What I need to do is periodically, I need to use that logging table and process the files that are not processed.... I am using a cursor to go through the file one by one... if say the processing fails on the 3rd file, I want to update the logging table with failure reasons and then continue on with the 4th file... Guys what is the best way to handle this scenario... any suggestion is appreciated. Do you think TRY Catch Error in Sql 2005 is helpful?Thanks |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-10 : 00:52:26
|
| I think I got it... I can use this approach..When you do iterative processing, there are cases when you do not want to exit the procedure on first error. You go through a set of rows that are handled independently, and if an operation fails for one row, you may still want to try to process remaining rows, possibly setting an error flag for the failed row. Here is an outline of such a procedure may look like: CREATE PROCEDURE error_demo_cursor AS DECLARE @err int, ... IF @@trancount > 0 BEGIN RAISERROR ('This procedure must not be called with a transaction in progress', 16, 1) RETURN 50000 END DECLARE some_cur CURSOR FOR SELECT id, col1, col2, ... FROM tbl WHERE status = 'New' ... SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE some_cur RETURN @err END OPEN some_cur SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE some_cur RETURN @err END WHILE 1 = 1 BEGIN FETCH some_cur INTO @id, @par1, @par2, ... SELECT @err = @@error IF @err <> 0 OR @@fetch_status <> 0 BREAK BEGIN TRANSACTION EXEC @err = some_sp @par1, ... SELECT @err = coalesce(nullif(@err, 0), @@error) IF @err <> 0 GOTO Fail INSERT other_tbl (...) SELECT @err = @@error IF @err <> 0 GOTO Fail UPDATE tbl SET status = 'OK' WHERE id = @id SELECT @err = @@error IF @err <> 0 GOTO Fail COMMIT TRANSACTION SELECT @err = @@error IF @err <> 0 BREAK -- Handle next guy CONTINUE Fail: ROLLBACK TRANSACTION UPDATE tbl SET status = 'Error' WHERE id = @id -- No error-checking here. END DEALLOCATE some_cur RETURN @errHere, if we get an error while we are handling the row, we don't want to exit the procedure, but only set an error status for this row and then move on to the next. The particular UPDATE statement where we set the status to 'Error' has no error checking, because – well, there is not really any action we can take if this UPDATE fails. If we for some reason cannot set the status, this is not reason to abort the procedure. As you see, there is a comment that explicitly says that there is no error checking, so that anyone who reviews the code can see that the omission of error checking is intentional.If you look closer, you see that in some cases we abort the procedure in case of an error even within the loop. We do so for FETCH, because the most likely error with a FETCH statement is a mismatch between the variables and the column list in the cursor. In this case, all executions of the FETCH statement will fail, so there is no reason to hang around. A similar reasoning applies when it comes to COMMIT TRANSACTION. Errors with COMMIT are so unexpected, that if they occur we have very little idea of what is going on, why the best is to leave here and now. |
 |
|
|
|
|
|
|
|
Cursor Continue On Failure