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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy