Eloquent way of logging errors in SQL Server2

I enjoy this template of error trapping.
It uses the TRY CATCH way of doing things, which is awesome.

To take this out for a spin:
Create the error table (just like inside Adventureworks):

CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NOT NULL,[UserName] [sysname] NOT NULL,[ErrorNumber] [int] NOT NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL, [ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL,CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

This table will be filled when there is an error.
The below procedure will be filled with SQL Server's famous @error_number

<><>

CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted -- by uspLogError in the ErrorLog table. AS BEGIN SET NOCOUNT ON; -- ditch the extra messages SET @ErrorLogID = 0; -- all is ok with the world BEGIN TRY IF ERROR_NUMBER() IS null -- no error? RETURN; IF XACT_STATE() = -1 -- uncommitable transaction? don't do any damage! BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + char(10) + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END; INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine],[ErrorMessage] ) VALUES( --CONVERT(sysname, CURRENT_USER), -- current_user is the owner (ie: dbo.) CONVERT(sysname,system_user), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), coalesce(ERROR_PROCEDURE(),'n/a'), ERROR_LINE(),ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SELECT @ErrorLogID =convert(nvarchar(16),@@IDENTITY); END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END;

Now, you can create any procedure and use the TRY CATCH method!

<><>

begin try begin transaction -- --insert a bad data type into a table insert into departments(deptnament) values ('a string that is way too long to append, way too long for sure absolutely no doubt to ') commit transaction End try Begin catch rollback transaction -----begin print error print'oops!' + char(10) + error_message() +char(10)+ 'error number: ' + convert(nvarchar(16),error_number()) ; ------end print error declare @err int set @err =error_number() ; execute uspLogError @err -- log the error here, passing the error # End catch -- test: select * from errorlog

And there you have your scripting for error logs. If you use a version of the above script, you will be able to track the errors way better
.