Resume on error. - RESOLVED - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Resume on error. - RESOLVED

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 12:45:12

I have the following cursor (I am using a cursor because I want to use the stored procedure. Inside the stored procedure I have a TRY, and catch statement.

If the procedure runs into a certain condition I raise a error using the line

RaiseError('My Error',16,1)

For some reason when this happens my cursor gets stuck in a loop because it just keeps trying to processess the same record (Basically when the error occurs the while statment starts over, so there is no fetch next).

Is there anyway to fetch next on error, so It doesn't get stuck on the same record?

Cursor code


Declare @GroupID varchar(50),@EmployerID int
Declare CurGroups Cursor for 
select a.GroupID,a.EmployerID 
from T_Mytable a
open CurGroups
	fetch Next from CurGroups into  @GroupID ,@EmployerID 
		BEGIN
			WHILE @@Fetch_Status = 0
				exec Sproc_ReGenerateEmployerHourly @GroupID
			select @GroupID
			fetch Next from CurGroups into  @GroupID ,@EmployerID 
		END
CLOSE curGroups 
deallocate curGroups

Stored Procedure



Create Procedure [dbo].[Sproc_ReGenerateEmployerHourly]
@GroupID Varchar(50)
as
Set NOCOUNT ON
Begin Try
	BEGIN
          RaiseError('My Error',16,1)
        END
END try
BEGIN CATCH
		IF (XACT_STATE()) <> 0 ROLLBACK
		DECLARE @ErrMsg varchar(4000), @ErrSeverity int
		set @ErrMsg = ERROR_MESSAGE()
		Set @ErrSeverity = ERROR_SEVERITY()
		RAISERROR(@ErrMsg, @ErrSeverity, @ErrSeverity)	
		return 0
END CATCH

how do I resolve this?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 13:33:36

The issue was in the BEGIN and ENd Statments. It should've been Post the WHile syntax, not prior. Just one of those days :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources