Use TRY…CATCH Over @@ERROR

Posted: August 11, 2010 in SQL Server 2005

For the most part, @@ERROR works fine, but it’s not as robust as exception handling. It does not give us the flexibility that a TRY…CATCH block construct does.

-- Create temp table
CREATE TABLE #Test (id INT IDENTITY, col1 VARCHAR(100));

BEGIN
   -- Start TRY block for insert statement
   BEGIN TRY
      -- Start the transaction
      BEGIN TRANSACTION;
      -- This will generate an error, as ID is an IDENTITY column and we can't insert a value in it
      INSERT INTO #Test(id, col1) VALUES (1, 'Test');  
      -- This statement will go through, commit the changes and show the result
      INSERT INTO #Test(col1) VALUES('Test');       
      -- If it gets here it is a success
      COMMIT;
      -- Check that the records was inserted
      SELECT * FROM #Test;
   END TRY
   -- Start CATCH block for insert statement
   BEGIN CATCH
      -- Handle rollback here
      IF @@TRANCOUNT > 0
         ROLLBACK;
      SELECT 'There was an error! ' + ERROR_MESSAGE();   -- See below for list of functions
   END CATCH
END;

-- Drop temp table
DROP TABLE #Test;

-- ERROR_NUMBER()     Returns the number of the error
-- ERROR_SEVERITY()   Returns the severity
-- ERROR_STATE()      Returns the error state number
-- ERROR_PROCEDURE()  Returns the name of the stored procedure or trigger where the error occurred
-- ERROR_LINE()       Returns the line number inside the routine that caused the error
-- ERROR_MESSAGE()    Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s