1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
| -- first lets build a temporary table to hold errors
IF (object_id('tempdb..#RAISERRORS') IS NULL)
CREATE TABLE #RAISERRORS (ErrorNumber INT, ErrorMessage VARCHAR(400), ErrorSeverity INT, ErrorState INT, ErrorLine INT, ErrorProcedure VARCHAR(128));
-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
DECLARE @tc AS INT;
SET @tc = @@trancount;
IF (@tc = 0)
BEGIN TRANSACTION;
ELSE
save TRANSACTION myTransaction;
-- the code in the try block will be executed
BEGIN try
DECLARE @return_value = '0';
SET @return_value = '0';
DECLARE
@ErrorNumber AS INT,
@ErrorMessage AS VARCHAR(400),
@ErrorSeverity AS INT,
@ErrorState AS INT,
@ErrorLine AS INT,
@ErrorProcedure AS VARCHAR(128);
-- assume that this procedure fails...
EXEC @return_value = [dbo].[AssumeThisFails]
IF (@return_value <> 0)
raiserror('This is my error message', 17, 1);
-- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
IF (@tc = 0)
commit TRANSACTION;
RETURN(0);
END try
-- the code in the catch block will be executed on raiserror("message", 17, 1)
BEGIN catch
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ERROR_PROCEDURE();
INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
-- if i started the transaction
IF (@tc = 0)
BEGIN
IF (XACT_STATE() <> 0)
BEGIN
SELECT * FROM #RAISERRORS;
ROLLBACK TRANSACTION;
INSERT INTO [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
SELECT * FROM #RAISERRORS;
INSERT [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
RETURN(1);
END
END
-- if i didn't start the transaction
IF (XACT_STATE() = 1)
BEGIN
ROLLBACK TRANSACTION myTransaction;
IF (object_id('tempdb..#RAISERRORS') IS NOT NULL)
INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
ELSE
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN(2);
END
ELSE IF (XACT_STATE() = -1)
BEGIN
ROLLBACK TRANSACTION;
IF (object_id('tempdb..#RAISERRORS') IS NOT NULL)
INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
ELSE
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN(3);
END
END catch
END |