-- ********
-- Demo - 4 
-- ********

-- 1 - Example

BEGIN TRY 

	T-SQL Statement

END TRY

BEGIN CATCH

	T-SQL Statement

END CATCH


-- 2 - Stored procedure
-- DROP PROCEDURE dbo.spErrorHandling 
CREATE PROCEDURE dbo.spErrorHandling AS 
-- Declaration statements
DECLARE @Error_Number int
DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line, 
Error_Procedure, UserName, HostName, Time_Stamp)

SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line, 
@Error_Procedure, @UserName, @HostName, @Time_Stamp
GO
 

-- 3 - Sample logging table
-- DROP TABLE [dbo].[ErrorHandling]
CREATE TABLE [dbo].[ErrorHandling](
[pkErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) COLLATE Latin1_General_BIN NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL DEFAULT ((1)),
[Error_Procedure] [varchar](200) COLLATE Latin1_General_BIN NOT NULL,
[Error_Line] [int] NOT NULL DEFAULT ((0)),
[UserName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
[HostName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),[Time_Stamp] datetime NOT NULL,
PRIMARY KEY CLUSTERED
(
[pkErrorHandlingID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


-- 4 - Verification process

-- Sample procedure 
-- DROP PROCEDURE dbo.spTest 
CREATE PROCEDURE dbo.spTest AS
BEGIN TRY

	SELECT 1/0

END TRY

BEGIN CATCH

	EXEC dbo.spErrorHandling 

END CATCH
GO

-- Execute the sample procedure
EXEC dbo.spTest;

-- Review the error handling data captured
SELECT *
FROM dbo.ErrorHandling;
 


