Error Handling in Stored Procedure is not executed when it is called via Entity Framework

Question

Hi,

I need some help to figure out why the error handling part in stored procedure was not executed if error encountered.

Here is the scenario, we use entity framework to communicate the app to our DB.

In my page, I called the stored procedure using entity framework and this stored procedure has error handling, I placed the script inside the TRY block, what ever error happens the CATCH block will capture the erros details and log/save it in our Error table.

To test it out, I have a column which is not nullable, I execute the Stored Procedure in backend passing the NULL parameter and error was captured. But when I did the same thing via front end, the CATCH block was not executed, and it only throws error in Catch block in front end.

Do you have any idea about this?

Thanks. 

Here is my SP

CREATE PROCEDURE InsertRecord
	@param					varchar(50)
AS
BEGIN
	------------------------------------------------------------------------------------
	-- (1). Initialize Variables
	------------------------------------------------------------------------------------
	DECLARE @classname		varchar(100),
			@functionname	varchar(100),
			@errordetails   varchar(4000),
			@intError		int
	
	SET @classname='Stored Proceudure'
	SET	@functionname='InsertRecord'
	SET @intError = 0
	
	SET NOCOUNT ON;

	BEGIN TRY
		------------------------------------------------------------------------------------
		-- (2). Insert record 
		------------------------------------------------------------------------------------
		INSERT INTO TABLENAME
		VALUES()....	
	END TRY
		
		------------------------------------------------------------------------------------  
		-- (3). LOG ERROR DETAILS ONCE ERROR ENCOUNTER
		------------------------------------------------------------------------------------  
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
			BEGIN
				SET @errordetails = @param + ' - ' + ERROR_MESSAGE()
				EXEC [InsertErrorLog] @classname,@functionname,@errordetails,@EMPLOYEEID	
			END
		END CATCH	
END

Answer 1

Hi,

I think what you are seeing is the result of an outer transaction rollback; EF wraps your call in a transaction, and that is rolled back client-side, also rolling back your error log record.

Edit: ...or that your rollback inside the catch clause is interfering with the EF transaction...

---

 

Cool tools for Linq-to-SQL and Entity Framework 4:  huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers  huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4