Stored procedure error handling in SQL Server 2000

Today, we’re continuing work on our multipart message aggregation upgrade for a future Esendex release.

One part of the investigations this morning has been looking into how we manage the database access as we add parts of an incoming message to it for later processing. As we have multiple servers processing inbound messages, there was the potential for two threads trying to write to the database simultanously with different parts of the same message. Both could look at the database and think there were no existing parts of a multipart message, and decide to try and insert a new multipart message record.

During this work we found out that SQL Server 2000 will still raise an SQLException error despite error handling put in T-SQL.

A quick mockup test yesterday revealed that two competing threads could indeed try and insert twice despite checking for an existing record and caused a Unique Key error 2601. As this was all being coded in a stored procedure we looked to see what error handling T-SQL provided us.

Whilst you can detect the error number after a T-SQL statement in a stored procedure by querying the global variable @@ERROR, you cannot prevent SQL Server 2000 from sending an exception error to the calling script. Therefore the following snippet does work, but you will still get an exception thrown. This is not the complete script but highlights the additional features which we used to get this working:

-- stored procedure snippet

-- declare a local variable to hold the error number
-- The global variable @@error gets reset after each
-- command so you would lose the error number following
-- the next statement should you wish to do extra things with it
-- like return it back to the script calling it

DECLARE @err int
...
-- update the parts received
UPDATE [PendingMultipartMessage] ...

-- check to see if the update ran by checking
-- the SET value of @multipartMessageID from the ID field

IF (@multipartMessageID is NULL)

BEGIN
  SET @multipartMessageID = NEWID()

  INSERT INTO [PendingMultipartMessage] (...) VALUES (...)

  SELECT @err = @@error

  IF (@err = 2601)

  BEGIN
    -- try updating instead of inserting as the record
    -- exists as the 2601 error says there's already a unique key
    UPDATE [PendingMultipartMessage] ...
  END

END

All that’s left to do is to wrap your stored procedure call in a try…catch wrapper and catch any SQLExceptions and check they’re not 2601. You can do what you like with the error, but choosing to ignore it means your application will no longer fail.

Improvements in SQL Server 2005 have afforded developers a TRY CATCH definition in T-SQL to help catch errors within stored procedures. This feels a lot more sensible as the database is the singular resource we’re trying to gain shared access to and it should be able to handle these errors internally without an extra callback. Just to remind you, the mainstream support for SQL Server 2000 ends in April 2008.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.