Monday, July 6, 2009

Error Saving SP and Table Structure in Database

Error Table Structure inorder to maintain Error data in database

CREATE TABLE [dbo].[PRJtblErrors](
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorDate] [datetime] NOT NULL DEFAULT (getdate()),
[UserName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('DBAdmin'),
[ModuleName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (db_name()),
[ScreenName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FunctionNameOrMethodName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

------------------------------------------
Call this procedure in the catch block
------------------------------------------

Sample
------
BEGIN
TRY

End Try
Begin Catch
Execute PRJspErrorInfo
End Catch

/**
-----------------------------------------------------------------------------------------
-- Stored Proc Name:
-- Author :
-- Date Created :
-- Version :
-- Last Revision :
-- Function :
-- :
-------------------------------------------------------------------------------------------
**/

CREATE PROCEDURE [dbo].[PRJspErrorInfoSave]
AS
BEGIN
INSERT INTO PRJtblErrors
(
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage
)
SELECT
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

No comments: