1:  set nocount on
   2:   
   3:  -- build a working table called x
   4:   
   5:  if (object_id('x') is not null) 
   6:      drop table x
   7:  create table x (col1 varchar(50))
   8:   
   9:  declare @sql nvarchar(500)
  10:  set @sql = 'insert into x select ''test'';'
  11:   
  12:  -- let's try some variations
  13:   
  14:  -- this works
  15:   
  16:  exec(@sql)
  17:   
  18:  if (select count(*) from x) != 1
  19:      print 'fail0'
  20:  truncate table x
  21:   
  22:  -- this works
  23:   
  24:  begin tran
  25:  exec(@sql)
  26:  rollback
  27:   
  28:  if (select count(*) from x) != 0
  29:      print 'fail1'
  30:  truncate table x
  31:   
  32:  -- this works
  33:   
  34:  begin tran
  35:  exec(@sql)
  36:  commit
  37:   
  38:  if (select count(*) from x) != 1
  39:      print 'fail2'
  40:  truncate table x
  41:   
  42:  -- this works
  43:   
  44:  exec('begin tran;' + @sql + 'rollback')
  45:   
  46:  if (select count(*) from x) != 0
  47:      print 'fail3'
  48:  truncate table x
  49:   
  50:  -- this works
  51:   
  52:  exec('begin tran;' + @sql + 'commit')
  53:   
  54:  if (select count(*) from x) != 1
  55:      print 'fail4'
  56:  truncate table x
  57:   
  58:  -- this works
  59:   
  60:  begin tran
  61:  exec('begin tran;' + @sql + 'commit')
  62:  commit
  63:   
  64:  if (select count(*) from x) != 1
  65:      print 'fail5'
  66:  truncate table x
  67:   
  68:  -- this works
  69:   
  70:  begin tran
  71:  exec('begin tran;' + @sql + 'commit')
  72:  rollback
  73:   
  74:  if (select count(*) from x) != 0
  75:      print 'fail6'
  76:  truncate table x
  77:   
  78:  /*
  79:   
  80:  -- this does NOT work
  81:   
  82:  begin tran
  83:  exec('begin tran;' + @sql + 'rollback')
  84:  commit
  85:   
  86:  if (select count(*) from x) != 0
  87:      print 'fail7'
  88:  truncate table x
  89:   
  90:  -- this does NOT work
  91:   
  92:  begin tran
  93:  exec('begin tran;' + @sql + 'rollback')
  94:  rollback
  95:   
  96:  if (select count(*) from x) != 0
  97:      print 'fail8'
  98:  truncate table x
  99:   
 100:  */
 101:   
 102:  -- clean up
 103:   
 104:  if (object_id('x') is not null) 
 105:      drop table x
 106:   
 107:  print 'done'