Click here to Skip to main content
15,888,521 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I am using a stored procedure which migrates an old record of a table (eg. Employee) to another table(eg. MigratedEmployee). For that I am using a stored procedure with try and catch...
I need to save the message that comes in ERROR_MESSAGE() in my table MigratedEmployee incase the INSERT fails...
How to do that??
SQL
CREATE PROCEDURE MigrateEmployee
-- Add the parameters for the stored procedure here
AS
BEGIN

DECLARE @errorMessage aS VARCHAR(MAX)
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	BEGIN TRY 
	--Insert record to Migrated Table
	END TRY
	BEGIN CATCH
	END CATCH
	--Insert record to Migrated table with error message
	END

Table A: Employee
Emp_Id | Emp_Name | Emp_Dept | Emp_IsActive
___________________________________________
Table B: MigratedEmployee
Emp_Id | Emp_NAme | Emp_Dept | Emp_IsActive | isActionFail | ErrorMessage
______________________________________________________________________


Please help
Posted
Updated 15-Dec-15 19:43pm
v2
Comments
Mohibur Rashid 16-Dec-15 1:57am    
I will tell you my experience about it. Bad idea. Try to write them on separate log file.
scarletwitch1990 16-Dec-15 2:07am    
well I did that, but I am using a variable in which the error message is getting stored and it is getting printed with the command PRINT but when I insert or update the same value at CATCH, the columns stays NULL.
The same thing when I try with SET @errorCode = @@ERROR,
the error code gets successfully saved in my table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900