Repeatedly execute a stored procedure

Question

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

Answer 1

You can perform error handling with the TRY CATCH functionality that's built in to SQL Server starting with SQL Server 2005. I'd recommend that you re-evaluate your query logic and look at something like the SQL CLR or a MERGE statement. Judging by the sample code, this should be a particularly easy route to take.

Answer 2

All this framework seems fine, so I'm not sure what your problem is (TRY/CATCH and re-throw errors if you have a lot of nested SPs).

However, set-based approaches are preferred. If you could give more of your requirements, we could probably suggest something easier. Typically for polling type scenarios, I usually use an agent job which operates on the entire set of new rows (perhaps with a cursor), or you can assign them a batch number and start processing of that batch. When the next time the agent job triggers, only new rows with no batch will get assigned another batch which will go off and get processed.

But without knowing more about your underlying motivation...