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

  • 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

    • Edited by Jerry Butiong Thursday, November 22, 2012 12:58 AM Inserted Code
    Thursday, November 22, 2012 12:53 AM

All replies

  • 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


    • Edited by KristoferA Thursday, November 22, 2012 1:31 AM
    Thursday, November 22, 2012 1:21 AM