62 lines
1.9 KiB
Transact-SQL
62 lines
1.9 KiB
Transact-SQL
|
|
CREATE PROCEDURE [dbo].[USP_LogError]
|
|
|
|
@ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted
|
|
-- by uspLogError in the ErrorLog table.
|
|
|
|
AS
|
|
BEGIN
|
|
--SET NOCOUNT ON;
|
|
|
|
-- Output parameter value of 0 indicates that error
|
|
-- information was not logged
|
|
SET @ErrorLogID = 0;
|
|
|
|
BEGIN TRY
|
|
-- Return if there is no error information to log
|
|
IF ERROR_NUMBER() IS NULL
|
|
RETURN;
|
|
|
|
-- Return if inside an uncommittable transaction.
|
|
-- Data insertion/modification is not allowed when
|
|
-- a transaction is in an uncommittable state.
|
|
IF XACT_STATE() = -1
|
|
BEGIN
|
|
--ROLLBACK tran
|
|
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
|
|
+ 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
|
|
RETURN;
|
|
END;
|
|
|
|
INSERT [dbo].[Error_Log]
|
|
(
|
|
[date],
|
|
-- [UserName],
|
|
[ErrorNumber],
|
|
[ErrorSeverity],
|
|
[ErrorState],
|
|
[ErrorProcedure],
|
|
[ErrorLine],
|
|
[ErrorMessage]
|
|
)
|
|
VALUES
|
|
(
|
|
GETDATE(),
|
|
-- CONVERT(sysname, CURRENT_USER),
|
|
ERROR_NUMBER(),
|
|
ERROR_SEVERITY(),
|
|
ERROR_STATE(),
|
|
ERROR_PROCEDURE(),
|
|
ERROR_LINE(),
|
|
ERROR_MESSAGE()
|
|
);
|
|
|
|
-- Pass back the ErrorLogID of the row inserted
|
|
SELECT @ErrorLogID = @@IDENTITY;
|
|
END TRY
|
|
BEGIN CATCH
|
|
PRINT 'An error occurred in stored procedure uspLogError: ';
|
|
-- EXECUTE [dbo].[uspPrintError];
|
|
RETURN -1;
|
|
END CATCH
|
|
END; |