Advertisements
Home > SQL Server Questions > Standardize TRY CATCH block across all Stored Procedures – MSDN TSQL forum

Standardize TRY CATCH block across all Stored Procedures – MSDN TSQL forum


–> Question:

I am trying to develop best try catch block to be standardized in all of the SPs. Below is what I have right now developed which is giving me proper formatted output, would like to add more details or remove unwanted items from it.

Thanks for all your help!

I am using 2008 R2 Version, would be migrating to 2012 soon.

BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
     ROLLBACK TRANSACTION
  END

DECLARE @ErrorMsg varchar(2100);

SET  @ErrorMsg =  ' DBName        = ' + DB_NAME() + CHAR(10)
+ ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047), LEFT(Error_Message(),2044))) + CHAR(10) 
+ ' Procedure     = ' + OBJECT_NAME(@@PROCID) + CHAR(10) + ' ErrorNumber   = ' + 
LTRIM(CONVERT(VARCHAR(9),Error_Number()))  + CHAR(10) 
+ ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State())) + CHAR(10)
+ ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))  + CHAR(10)
+ ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))  + CHAR(10)
+ ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121) + CHAR(10) + ' ErrorBy = ' + SUSER_SNAME();

RAISERROR(@ErrorMsg,16,1);
    RETURN -1
END CATCH

 

–> My Answer:

The way you’ve drafted your TRY-CATCH looks good, however you can also consider few things:

1. Use THROW instead of RAISERROR as recommended by Microsoft, this will also reduce unnecessary (at times) declaration and assignment of error related variables, link.

2. With THROW you can also catch multiple errors, RAISERROR will only throw last error raised, link.

3. Make use of XACT_ABORT only when you want to rollback the entire transaction, link.

Check all these and other stuff about TRY-CATCH in my blog posts.
 

Ref Link.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: