sp_addmessage 50001,
              16,
			  N'error'


sp_dropmessage 50001

RAISERROR (50001, 16, 2)



create 
--ALTER
PROC Retry (@retryCount int OUTPUT,
@error_message NVARCHAR(MAX) OUTPUT,
@error_severity INT OUTPUT,
@error_state INT OUTPUT
)
AS
print 'start'

IF @retryCount IS NULL SET @retryCount = 3
SET @error_message = ERROR_MESSAGE()
+ N' from procedure ' + ISNULL(ERROR_PROCEDURE(), '?')
+ N' line ' + CAST(ERROR_LINE() AS NVARCHAR(8)) 
SET @error_severity = ERROR_SEVERITY();
SET @error_state = ERROR_STATE();
IF @error_state = 0 SET @error_state = 1
IF ERROR_NUMBER() in (1205, 50001)
IF @retryCount > 0 BEGIN
print 'rt'
SET @retryCount = @retryCount - 1
IF XACT_STATE() <> 0 ROLLBACK TRAN
RETURN 0
END
-- do error processing here
print 'end'
RAISERROR (@error_message, @error_severity, @error_state)

Start:
BEGIN TRY
DECLARE @retryCount INT
DECLARE @error_message NVARCHAR(MAX)
DECLARE @error_severity INT
DECLARE @error_state INT
RAISERROR (50001, 16, 1)
END TRY
BEGIN CATCH
EXEC Retry @retryCount OUTPUT,
@error_message OUTPUT,
@error_severity OUTPUT,
@error_state OUTPUT
IF @retryCount <> 0 GOTO Start
RAISERROR (@error_message, @error_severity, @error_state)
END CATCH




DECLARE @r INT
SET @r = 2
EXEC Retry @r OUTPUT
SELECT @r

IF 2 in (2,3)
PRINT 'got here'



DECLARE @retryCount int
SET @retryCount = 3
Retry: BEGIN TRY
BEGIN TRAN
-- do work here
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @error_message NVARCHAR(MAX)
SET @error_message = ERROR_MESSAGE()
+ N' from procedure ' + ISNULL(ERROR_PROCEDURE(), '?')
+ N' line ' + ERROR_LINE() 
DECLARE @error_severity INT
SET @error_severity = ERROR_SEVERITY();
DECLARE @error_state INT
SET @error_state = ERROR_STATE();
IF @error_state = 0 SET @error_state = 1
IF ERROR_NUMBER() = 1205
IF @retryCount > 0 BEGIN
SET @retryCount = @retryCount - 1
IF XACT_STATE() <> 0 ROLLBACK TRAN
GOTO Retry
END
-- do error processing here
RAISERROR (@error_message, @error_severity, @error_state)
END CATCH



BEGIN TRY
INSERT INTO pubs..jobs VALUES ('asdf', 3, 10)
END TRY
BEGIN CATCH
IF ERROR_STATE() <> 0 ROLLBACK TRAN
DECLARE @m NVARCHAR(MAX)
SET @m = ERROR_MESSAGE() + N' ' + CAST(ERROR_LINE() AS VARCHAR(MAX))
select @m
END CATCH

END TRY
BEGIN CATCH
PRINT 'outer'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()

END CATCH


CREATE 
--ALTER
PROC ReRaiserror(@procedure NVARCHAR(MAX), @line INT
AS
DECLARE @error_message NVARCHAR(MAX)
SET @error_message = ERROR_MESSAGE() 
DECLARE @error_severity INT
SET @error_severity = ERROR_SEVERITY();
DECLARE @error_state INT
SET @error_state = ERROR_STATE();
IF @error_state = 0 SET @error_state = 1
RAISERROR (@error_message, @error_severity, @error_state)





BEGIN TRY
BEGIN TRAN
INSERT INTO pubs..jobs VALUES ('range test', 1, 30)
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT ERROR_STATE()
PRINT ERROR_NUMBER()
END CATCH


RAISERROR('test', 16, 0)


BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO




BEGIN TRY


BEGIN TRY
    -- Generate check constraint error.
    -- this produces a 547 error
    INSERT INTO pubs..jobs VALUES ('test', 1, 30);
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
        IF @ErrorState = 0 SET @ErrorState = 1
    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    -- this will return original error message
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;


PRINT @@ERROR

END TRY
BEGIN CATCH
PRINT ERROR_STATE()
END CATCH
