Stored procedures using old-style error handling and
savepoints may not work as intended when they are used together with TRY …
CATCH blocks. I will provide some examples. This post continues the series on
defensive database programming.
Avoid calling old-style stored
procedures from TRY blocks.
Stored procedures using old-style error handling may not
work as expected when invoked from TRY blocks. Consider the following procedure:
CREATE PROCEDURE dbo.GetRatio
@n1 FLOAT,
@n2 FLOAT
AS
BEGIN
SET XACT_ABORT OFF
DECLARE
@ErrorCode INT
SELECT @n1/@n2 AS Ratio
SELECT
@ErrorCode = @@ERROR
PRINT 'Execution here'
IF @ErrorCode
<> 0 BEGIN
SELECT
@ErrorCode AS ErrorCode
END
END
Clearly sometimes it handles errors as originally intended:
EXEC dbo.GetRatio 5, 0
Ratio
----------------------
Msg 8134, Level 16, State 1, Procedure GetRatio,
Line 8
Divide by zero error encountered.
Execution here
ErrorCode
-----------
8134
However, inside a TRY block the same procedure works
differently:
BEGIN TRY
EXEC dbo.GetRatio 5, 0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
AS ERROR_NUMBER,
ERROR_MESSAGE() AS ERROR_MESSAGE;
END CATCH
Ratio
----------------------
(0 row(s) affected)
ERROR_NUMBER ERROR_MESSAGE
------------
--------------------------------
8134 Divide by
zero error encountered.
Note that this time the execution breaks before it reaches
the PRINT command.
Avoid using savepoints with TRY …
CATCH blocks.
The reason is simple: sometimes when the execution reaches
your CATCH block, the transaction may be doomed. A doomed transaction cannot be
rolled back to a savepoint, it can only be rolled back completely. For example,
consider the following table and procedure:
CREATE TABLE [data].[Codes](
[Code] [varchar](10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT
PK_Codes PRIMARY KEY(Code)
) ON [PRIMARY]
GO
CREATE PROCEDURE Writers.InsertCode
@Code VARCHAR(10),
@Description
VARCHAR(40)
AS
BEGIN
DECLARE @ret INT, @InTransaction INT;
SET
@InTransaction = @@TRANCOUNT;
BEGIN TRY;
IF
@InTransaction = 0 BEGIN
BEGIN TRAN InsertCode;
END ELSE BEGIN
SAVE TRAN InsertCode;
END;
INSERT INTO [data].[Codes]
([Code],[Description])
VALUES(@Code, @Description);
COMMIT;
SET @ret = 0;
END TRY
BEGIN CATCH
ROLLBACK TRAN InsertCode;
SET @ret = ERROR_NUMBER();
SELECT @ret AS
ERROR_NUMBER,
ERROR_MESSAGE() AS ERROR_MESSAGE;
END CATCH
RETURN @ret;
END
GO
CREATE TRIGGER NoCodesWithBackslash ON
[data].[Codes]
FOR INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted WHERE
Code LIKE '%\%') BEGIN
RAISERROR('Code cannot contain
backslash', 16,
1);
END
END
Try to invoke it in the middle of an outstanding transaction:
DECLARE @ret INT;
BEGIN TRAN
INSERT INTO [data].[Codes]
([Code]
,[Description])
VALUES
('Code1'
,'Description1')
EXEC @ret = Writers.InsertCode
@Code = 'Code\1',
@Description =
'Description1'
SELECT @ret AS Ret
Msg 3931, Level 16, State 1,
Procedure InsertCode, Line 21
The current transaction
cannot be committed and cannot be rolled back to a savepoint. Roll back the
entire transaction.
As you have seen, you cannot roll back a doomed transaction
to a savepoint.
Note: not every
error renders your transaction doomed. The following script also generates an
error, but the error handling works, because the transaction is not doomed:
DECLARE @ret INT;
BEGIN TRAN
SELECT @@TRANCOUNT AS
TRANCOUNT, TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION
INSERT INTO [data].[Codes]
([Code]
,[Description])
VALUES
('Code1'
,'Description1')
EXEC @ret = Writers.InsertCode
@Code = 'Code1',
@Description =
'Description1'
SELECT @ret AS Ret
SELECT * FROM [data].[Codes]
COMMIT
TRANCOUNT TRANSACTION_ID
-----------
--------------------
1 32583
ERROR_NUMBER ERROR_MESSAGE
------------ ---------
2627 Violation of PRIMARY KEY constraint
'PK_Codes'. Cannot insert duplicate key in object 'data.Codes'.
Ret
-----------
2627
Code Description
----------
----------------------------------------
Code1
Description1
Clearly the first insert was not rolled back, so the error handling worked as expected.
As you have seen, if you are testing how your TRY..CATCH
block handles errors, you must include cases when your transaction is rendered
doomed.
Next post in this series:
Defensive
database programming: fun with changing column widths.