Skip to content

Instantly share code, notes, and snippets.

@chilversc
Created January 13, 2015 17:31
Show Gist options
  • Save chilversc/b085ec9885221edb8692 to your computer and use it in GitHub Desktop.
Save chilversc/b085ec9885221edb8692 to your computer and use it in GitHub Desktop.
T-SQL nesting transactions for stored procedures
DECLARE @mark CHAR(32) = replace(newid(), '-', '');
DECLARE @trans INT = @@TRANCOUNT;
IF @trans = 0
BEGIN TRANSACTION @mark;
ELSE
SAVE TRANSACTION @mark;
BEGIN TRY
-- do work here
IF @trans = 0
COMMIT TRANSACTION @mark;
END TRY
BEGIN CATCH
IF xact_state() = 1 OR (@trans = 0 AND xact_state() <> 0) ROLLBACK TRANSACTION @mark;
THROW;
END CATCH
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment