THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Avoid mixing old and new styles of error handling.

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.

 

Published Saturday, November 15, 2008 11:27 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ranga said:

Thanks for the examples...really helpful.

November 17, 2008 12:54 PM
 

Alexander Kuznetsov said:

The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN DO SOMETHING;

November 27, 2008 10:29 PM
 

Alexander Kuznetsov said:

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI

December 8, 2008 11:04 AM
 

Alexander Kuznetsov said:

Suppose that you need to implement the following logic: IF(row exists) Update the row ELSE Insert a new

December 14, 2008 8:37 PM
 

Alexander Kuznetsov said:

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.

January 25, 2009 5:57 PM
 

Alexander Kuznetsov said:

I have been posting examples of defensive database programming for some time now. I am by no means done

March 8, 2009 9:49 PM
 

Alexander Kuznetsov said:

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure

March 21, 2009 11:05 PM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

Dems said:

I disagree with not using save points and TRY/CATCH

You can test for a doomed transaction with XACT_STATE()

BEGIN CATCH

 IF (XACT_STATE() <> 0)

 BEGIN

   ROLLBACK TRAN InsertCode;

   SET @ret = ERROR_NUMBER();

   SELECT    @ret AS ERROR_NUMBER, ERROR_MESSAGE() AS ERROR_MESSAGE;

 END

 ELSE

 BEGIN

   -- ROLLBACK TRAN?

   -- Maybe re-raise the error?

   -- Whatever fits your code's purpose.

 END

END CATCH

June 17, 2009 7:06 AM
 

Dems said:

Ooops, should have said...

IF (XACT_STATE() <> -1)  -- Not 0 *doh*

June 17, 2009 7:07 AM
 

Alexander Kuznetsov said:

Dems,

Yes definitely "you can test for a doomed transaction with XACT_STATE()", and then what? If your transaction is doomed you simply cannot roll back to a savepoint.

June 17, 2009 10:36 AM
 

Alexander Kuznetsov said:

My query used to work, but it blows up after I have added an index? The following query is not safe:

July 11, 2009 11:14 PM
 

Alexander Kuznetsov said:

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order -

July 16, 2009 5:41 PM
 

Alexander Kuznetsov said:

In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider

October 9, 2009 4:41 PM
 

Khawaja said:

December 7, 2011 6:32 AM
 

Alexander Kuznetsov said:

Khawaja,

What is your point?

December 7, 2011 9:08 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement