I have a sproc that I am calling from C# with a transaction by doing:
using (var dbContext = PowerToolsDatabase.GetDataContext())
{
dbContext.Connection.Open();
using (dbContext.Transaction = dbContext.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
foreach (var element in package.AddOrUpdateElements)
{
dbContext.usp_Element_Commit( /* args */);
}
dbContext.Transaction.Commit();
}
}
And in that sproc there is a try catch, and a RAISERROR inside the try part that is executed under certain circumstances
BEGIN TRY
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Perform Name Uniqueness check (for new)
IF EXISTS ( SELECT PK.identifier --... )
BEGIN
RAISERROR(60000, 16, 1, 'dbo.usp_Element_Commit', 'Supplied Element Name (for new Element) already exists')
RETURN
END
-- Do stuff
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = 'dbo.usp_Element_Commit -- ' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
When I run that and hit the RAISERROR inside the try section of the sproc I get the following multiple errors:
dbo.usp_Element_Commit -- Supplied Element Name (for new Element) already exists
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
What is the recommended way to handle errors and and jump to the catch block without messing up the external transaction?
Also if I remove the rollback from the catch block in the sproc then I get the same Transaction Count message with Previous Count = 1, current count = 2