August 10, 2009 at 6:17 am
Hey GUys,
I do not need the cursor to break after failures and want to iterate through all the records... Any suggestion how can I change this code below? Thanks!
BEGIN TRY
DECLARE test_cursor CURSOR STATIC FOR
SELECT [file_name]
, op_format
FROM ABC..test --- THIS IS FOR TESTING PURPOSE!!!!!!!!!
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @var1, @var2
WHILE @@FETCH_STATUS = 0
BEGIN
<<<
Update log_table
set record_count = @rowcnt
where [file_name] = @var1
FETCH NEXT FROM test_cursor INTO @file_name, @delimeter_type
END
CLOSE test_cursor
DEALLOCATE test_cursor
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK
SET @error_msg = error_message()
UPDATE log_table
SET failure = 'file cannot be processed due to error: ' + @error_msg + '; at line ' + cast(error_line() as varchar)
WHERE [file_name] = @var1
END CATCH
-- ============
August 10, 2009 at 7:17 am
If you want that your code will continue to run in case of a runtime error instead of getting to the catch block, you’ll have to delete the catch try block and hope that the runtime error won’t cause the batch/procedure termination.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2009 at 9:03 am
Hi Ghanta,
If you want to continue on after the error, you'll need to do your error handling inside the cursor.
Here's your code, rewritten a little (it's just an example, as I'm sure your's was):
DECLARE @rowcnt
SET @rowcnt = 1
DECLARE test_cursor CURSOR STATIC FOR
SELECT [file_name], op_format
FROM ABC..test --- THIS IS FOR TESTING PURPOSE!!!!!!!!!
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @var1, @var2
WHILE (@@FETCH_STATUS = 0 ) BEGIN
BEGIN TRY
Update log_table
set record_count = @rowcnt
where [file_name] = @var1
SET @rowcnt = @rowcnt + 1
END TRY
BEGIN CATCH
SET @error_msg = error_message()
UPDATE log_table
SET failure = 'file cannot be processed due to error: ' + @error_msg + '; at line ' + cast(error_line() as varchar)
WHERE [file_name] = @var1
END CATCH
FETCH NEXT FROM test_cursor INTO @var1, @var2
END
CLOSE test_cursor
DEALLOCATE test_cursor
Mark
Just a cog in the wheel.
August 13, 2009 at 4:06 pm
You may want to think about nesting a TRY/CATCH similar to the following. Based on what starunit came up with above I just wrapped a TRY/CATCH around all his code. Here is a possible way to keep all code a little safer. By having a TRY/CATCH for the specific area to continue processing as desired nested in a TRY/CATCH. Also you may think about using a local fastforward cursor (or a way to replace cursor altogether).
BEGIN TRY
DECLARE @rowcnt
SET @rowcnt = 1
DECLARE test_cursor CURSOR LOCAL STATIC FOR
SELECT [file_name], op_format
FROM ABC..test --- THIS IS FOR TESTING PURPOSE!!!!!!!!!
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @var1, @var2
WHILE (@@FETCH_STATUS = 0 ) BEGIN
BEGIN TRY
Update log_table
set record_count = @rowcnt
where [file_name] = @var1
SET @rowcnt = @rowcnt + 1
END TRY
BEGIN CATCH
SET @error_msg = error_message()
UPDATE log_table
SET failure = 'file cannot be processed due to error: ' + @error_msg + '; at line ' + cast(error_line() as varchar)
WHERE [file_name] = @var1
END CATCH
FETCH NEXT FROM test_cursor INTO @var1, @var2
END
CLOSE test_cursor
DEALLOCATE test_cursor
END TRY
BEGIN CATCH
SET @curStatus = Cursor_Status('local', 'test_cursor'); --set it to LOCAL above, if using global above change here too
IF @curStatus >= 0
BEGIN
CLOSE objectsCur;
DEALLOCATE objectsCur;
END
ELSE IF @curStatus = -1 --may have been closed already so just deallocate
BEGIN
DEALLOCATE objectsCur;
END;
--set up as desired here
SELECT ERROR_NUMBER() [ErrorNumber]
,ERROR_SEVERITY() [ErrorSeverity]
,ERROR_STATE() [ErrorState]
,ERROR_PROCEDURE() [ErrorProcedure]
,ERROR_LINE() [ErrorLine]
,ERROR_MESSAGE() [ErrorMessage];
END CATCH;
Don't forget you can nest several TRY/CATCHes in there or have several nestings as needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply