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
