Thursday, March 1, 2012

Adding critical logging to your procedures

Here is a little tip that will help produce detailed logging and debugging reference points:

Output the name of the stored procedure when it runs.  Simple.

Procedure names, much like anything else, can change over time.  So how do we keep up changes to those messy output strings?  You don't, Simple again.

Include this little jewel with your print output or logging output and see how easy it is to find your way back to the stored proc:
SELECT OBJECT_SCHEMA_NAME(@@PROCID) as [schema], OBJECT_NAME(@@PROCID) as [procedure]
Maybe really show off what a fancy coder you are by including it in your error handling:
BEGIN CATCH
     SELECT
     OBJECT_SCHEMA_NAME(@@PROCID) as [schema],
     OBJECT_NAME(@@PROCID) as [procedure],    

     ERROR_NUMBER() AS ErrorNumber,     
     ERROR_SEVERITY() AS ErrorSeverity,     
     ERROR_STATE() AS ErrorState,     
     ERROR_PROCEDURE() AS ErrorProcedure,     
     ERROR_LINE() AS ErrorLine,     
     ERROR_MESSAGE() AS ErrorMessage;
END CATCH;