How to Insert Error_Message() into table and return user defined Message

Robymon

I want to insert the sql Error Message into the Error Table and return the user defined Error Message. Below is my code, if it sucess then Return Null else it should return Error message like 'Error on Inserting' at the same time it should save the actual error details in the Error table Create PROCEDURE addTitle AS SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION Create table #temp(ID int,fName varchar(20)) Insert into #temp(ID,fName)values('a1','test') COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION Insert into apl_Error(Error_Desc,Error_Date) select RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(),GETDATE() END CATCH Return

Corporal Agarn

You will need a "translation" table that gives the message you want based on the error.

gvprabu

Hi, Try like this... If you need add TRANSACTION also in this Code.

CREATE PROCEDURE addTitle
@ErrorDtls VARCHAR(100) OUTPUT
BEGIN
BEGIN TRY
-- Insert Statement
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
SELECT @ErrorDtls = NULL
END TRY
BEGIN CATCH -- While Error this Block will work
-- Insert Error table
Insert into apl_Error(Error_Desc,Error_Date)
SELECT RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(), GETDATE()
-- Frame Error Message
SELECT @ErrorDtls ='Error on Inserting'

END CATCH

END

-- Execute Statement
DECLARE @ErrorDtls VARCHAR(100)
EXEC addTitle @ErrorDtls=@ErrorDtls OUTPUT
SELECT @ErrorDtls

GVPRabu

prakash chakrala

Hi First create table

CREATE TABLE apl_Error
(
Error_Desc VARCHAR(MAX),
Error_Date DATE
)

After that create this procedure

CREATE PROC SAMPLE
@ERROR_DESC VARCHAR(MAX) OUTPUT
AS

BEGIN
BEGIN TRY
CREATE TABLE #TABLE(ID int,fName varchar(20))
INSERT INTO #TABLE VALUES('A1','CHOICE')
SELECT @ERROR_DESC=NULL
END TRY

BEGIN CATCH
INSERT INTO apl\_Error(Error\_Desc,Error\_Date)
SELECT cast( RTRIM(LTRIM(CAST(ERROR\_STATE() as CHAR(10)))) +','+ RTRIM(LTRIM(CAST(ERROR\_LINE() AS CHAR(5))))+','+ERROR\_PROCEDURE()+','+ERROR\_MESSAGE() as varchar(max)),GETDATE()

SELECT @ERROR\_DESC='Error on Inserting'		


END CATCH

END

to execute this procedure

DECLARE @NAME VARCHAR(MAX)
EXEC SAMPLE @ERROR_DESC=@NAME OUTPUT
SELECT @NAME AS Error_Description

then see apl_Error table now you can see the message and date.

Error_Desc Error_Date
1,8,SAMPLE,Conversion failed when converting the varchar value 'A1' to data type int. 2013-07-05