2

I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using that same context. My question is will the stored procedure be rolled back as well in the case of an exception.

Below is how the SP is structured

CREATE PROCEDURE dbo.UpdateObject
@ObjectId VARCHAR(20),@ObjectStatus VARCHAR(20)='Yes'
AS 
SET NOCOUNT ON;

DECLARE @object_status_enddate  DATE;
DECLARE @object_optout VARCHAR(10);
DECLARE @current_date  DATE=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','-');

BEGIN TRY
    SELECT TOP(1)
     @object_status_enddate=a.EndDate,@object_optout=a.OptOut
    FROM xxx a
    WHERE ObjectId=@ObjectId
    ORDER BY LastModified DESC;
    --TERM EXISTING STATUS
    WITH CurrentStatus AS
    (
    SELECT TOP(1) *,
    ROW_NUMBER() OVER (ORDER BY LastModified DESC) AS RN
    FROM XXX
    WHERE ObjectId= @ObjectId
    )
    UPDATE CurrentStatus SET EndDate = @current_date, LastModified = CURRENT_TIMESTAMP


    --ADD NEW STATUS
    INSERT INTO [dbo].[XXX](ObjectId,ObjectIndicator,StartDate,EndDate,LastModified,ModifiedBy,OptOut)
    SELECT @ObjectId,@ObjectStatus,@current_date,@object_status_enddate,CURRENT_TIMESTAMP,USER,@object_optout
END TRY
BEGIN CATCH
    --SOMETHING WENT WRONG
    DECLARE @ErrorMessage NVARCHAR(MAX),@ErrorSeverity INT,@ErrorState INT;
    SELECT @ErrorMessage=ERROR_MESSAGE()+' Line '+CAST(ERROR_LINE() AS NVARCHAR(5)),@ErrorSeverity=ERROR_SEVERITY(),@ErrorState=ERROR_STATE();
    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;
GO
  • Probably depends entirely on the DBMS. And definitely depends on whether said stored procedure has any commit statements in it, or invokes anything that does en explicit or implicit commit. – Colin 't Hart Jun 27 '14 at 16:06
  • @Colin'tHart I'm using SQL Server 2012. There are no commits, only a try and catch block. I will post an example above. – Antarr Byrd Jun 27 '14 at 17:06
  • 1
    Then it should be completely transactional, ie: a rollback will also rollback all of the actions done by the stored procedure. You've written it just like it should be done -- ie: without a commit in the stored procedure -- so that it can be used in this way. The commits should always be done in the calling environment. Another way of putting it: do the commits at an as high level as possible so that you benefit most from re-use of these atomic blocks. – Colin 't Hart Jun 27 '14 at 20:19
1

The rollback will rollback all opening (active) transaction. In case using Entity Framework, EF always auto create an root (outermost) transaction, so that the rollback inside a stored proc will rollback the transaction created by EF as well and it will cause error.

There is a way to get over, using Save point -> see https://msdn.microsoft.com/en-us/library/ms188378.aspx

I've applied the template save point for EF and it works well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.