1.Sample Table CREATE TABLE ErrorLog
(
LogID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ProcedureName VARCHAR(150) NULL,
ErrorMessage VARCHAR(MAX),
ErrorDate DATETIME
)
2.Sample Procedure CREATE PROC Usp_Test
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err VARCHAR(MAX)
BEGIN TRY
SELECT 1/0 /*I have forced DIVIDE BY ZERO Error */
END TRY
BEGIN CATCH
SELECT @Err = 'Line No :' + CAST(ERROR_LINE() AS VARCHAR) + ', Description :' + ERROR_MESSAGE()
INSERT ErrorLog(ProcedureName,ErrorMessage,ErrorDate)
SELECT ERROR_PROCEDURE(), @Err, GETDATE()
END CATCH
END
3. Execute the Procedure EXEC Usp_Test
4.To See the Errors Logged SELECT * FROM ErrorLog
5.Result
LogID ProcedureName ErrorMessage ErrorDate
1 Usp_Test Line No :9, Description :Divide by zero error encountered. 2013-07-23 18:48:51.030
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator