Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@KirillPashkov
Last active November 24, 2017 09:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save KirillPashkov/8bf3aea59c311969c5968af8594d2adf to your computer and use it in GitHub Desktop.
Save KirillPashkov/8bf3aea59c311969c5968af8594d2adf to your computer and use it in GitHub Desktop.
-- Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
set nocount on
go
set cursor_close_on_commit off
go
if object_id('tempdb..#t', 'U') is not null
drop table #t
create table #t (num int)
insert #t values (1),(2),(3),(4);
begin tran MyTran
declare @i int
declare @t table (num int)
declare mycursor cursor for
select num from #t
open mycursor
fetch mycursor into @i
while @@FETCH_STATUS=0
begin
--save tran MyCheckPoint
begin try
print @i
if @i = 2
raiserror('Eto fiasko, bratan!', 16, 1)
insert into @t values (@i)
end try
begin catch
rollback tran MyTran --MyCheckPoint
GoTo Next_item
end catch
Next_item:
fetch next from mycursor into @i
end
close mycursor
deallocate mycursor
select * from @t
print '@@TRANCOUNT ' + convert(varchar, @@TRANCOUNT)
if @@TRANCOUNT > 0
commit tran MyTran
go
set cursor_close_on_commit on
go
set nocount off
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment