EXEC sp_addmessage 50001,
              16,
			  N'retryable error %s'


EXEC sp_addmessage 50002,
              16,
			  N'error %s'


EXEC sp_addmessage 60000,
			10,
N'Reraised Error %d, Level %d, State %d, Procedure %s, Line %d, Message: %s';



CREATE
PROC ReRaise
as
DECLARE @error_message NVARCHAR(MAX)
SET @error_message = ERROR_MESSAGE();
DECLARE @error_number INT
SET @error_number = ERROR_NUMBER() 
DECLARE @error_severity INT
SET @error_severity = ERROR_SEVERITY();
DECLARE @error_state INT
SET @error_state = ERROR_STATE();
IF @error_state < 1 SET @error_state = 1
DECLARE @error_procedure NVARCHAR(200)
SET @error_procedure = ISNULL(ERROR_PROCEDURE(), '?');
DECLARE @error_line INT
SET @error_line = ERROR_LINE()
RAISERROR(60000,
@error_severity,
1,
@error_number,
@error_severity,
@error_state,
@error_procedure,
@error_line,
@error_message
)

CREATE
PROC Retry(@retryCount INT OUTPUT)
AS
PRINT 'retry'
IF ERROR_NUMBER() in (1206, 50001)
BEGIN
  IF @retryCount IS NULL SET @retryCount = 3
    IF @retryCount > 0
    BEGIN
      SET @retryCount = @retryCount - 1
      RETURN 1 -- retry expression
    END
    ELSE
      -- add logging as appropriate
      SET @retryCount = NULL;
      RETURN 2 -- retry expired
END
RETURN 0 -- not retryable

CREATE
--ALTER
PROC ErrorTest(@error INT)
AS
TryAgain:
BEGIN TRY
-- simulate error
RAISERROR (@error, 16, 1, 'oops')
END TRY
BEGIN CATCH
DECLARE @status INT
DECLARE @retryCount INT
EXEC @status = Retry @retryCount OUTPUT
IF @status = 1
GOTO TryAgain
IF @status = 2
BEGIN
-- retry expired, clean up as necessary
-- just so you can see it happening
print 'do retry'
-- do a reraise if that makes sense
EXEC ReRaise
RETURN
END
-- if you get to here it is
-- not retryable, clean up as 
-- for other errors
PRINT 'cleanup'
-- if you want to reraise
EXEC ReRaise
END CATCH

EXEC ErrorTest 50001
EXEC ErrorTest 50002

CREATE
--ALTER
PROC ErrorTestW(@error INT)
AS
DECLARE @status INT
WHILE ISNULL(@status, 1) > 0
BEGIN TRY
-- simulate an error
RAISERROR (@error, 16, 1, 'oops')
END TRY
BEGIN CATCH
DECLARE @retryCount INT
EXEC @status = Retry @retryCount OUTPUT
IF @status = 2
BEGIN
-- just so you can see it happening
print 'do retry'
EXEC ReRaise
RETURN
END
IF @status = 0
BEGIN
-- if you get to here it is
-- not retryable, clean up as 
-- for other errors
PRINT 'cleanup'
-- if you want to 
EXEC ReRaise
END
END CATCH
GO


EXEC ErrorTestW 50001
EXEC ErrorTestW 50002


select * from sys.messages
