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
Best Answer
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.