Wednesday, July 27, 2016

T-SQL transaction control in SP

A very basic usages of transactions in order to ensure that only one transaction is active at a time.

use master
go

declare @trancount INT = @@TRANCOUNT
, @i INT = NULL

begin try
print 'start'
print @trancount

IF @trancount = 0
begin
BEGIN TRANSACTION
print 'trans'
print @trancount
end

print 'working'
print @trancount

-- Uncoment this line to create exception
-- set @i = 1/0

IF @trancount = 0
BEGIN
COMMIT
print 'commit'
print @trancount
END

END TRY
BEGIN CATCH
print 'rolling'
print @trancount
print ERROR_MESSAGE()

IF @trancount = 0
BEGIN
ROLLBACK
print 'roll'
print @trancount
END
END CATCH