I have a situation where I need to repeatedly execute a stored procedure Now this procedure (spMAIN) has a cursor inside which looks for a value from a table as T1,which has the following structure

ID     Status
----   --------
1      New
2     New
3     success
4     Error

now the cursor looks for all rows with a status of 'New'

Now while processing , if that instance of the cursor encounters an error, another SP say spError needs to be called, the 'Status' column in T1 needs to be updated to 'Error' and spMAIN needs to be called again which again repeats the process, looking for rows with 'new'

how do I do it? Also, also, while we are at it, what if an SP has other SPs inside it and if any of those SP raises an error, same thing needs to be done, the T1 table needs to be updated ('Error') and spMAIN needs to be called again.

can you also recommend something ?

here's some code

ALTER PROC zzSpMain
AS 
   BEGIN
       DECLARE @id INT
      BEGIN TRY
         IF EXISTS ( SELECT   *
                     FROM     dbo.zzTest
                     WHERE    istatus = 'new' ) 
            BEGIN


               DECLARE c CURSOR
                  FOR SELECT  id
                      FROM    zztest
                      WHERE   istatus = 'new'

               OPEN c 
               FETCH NEXT FROM c INTO @id

               WHILE @@FETCH_STATUS = 0
                  BEGIN 
                     PRINT @id


                     IF @id = 2 
                        BEGIN           
                           UPDATE   zztest
                           SET      istatus = 'error'
                           WHERE    id = @id
                           RAISERROR ( 'Error occured', 16,
                              1 )
                        END 

                     UPDATE   zztest
                     SET      istatus = 'processed'
                     WHERE    id = @id

                     FETCH NEXT FROM c INTO @id
                  END
               CLOSE c
               DEALLOCATE c

            END

      END TRY   
      begin CATCH

         EXEC zzSpError
      END CATCH 

   END