DROP TABLE IF EXISTS testtbl
CREATE TABLE testtbl 
    (id      int  NOT NULL,
     datecol date NOT NULL,
     CONSTRAINT pk_testtbl PRIMARY KEY (id))
go
CREATE OR ALTER PROCEDURE inner_sp AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   PRINT '---inner_sp starting'

   --BACKUP DATABASE master TO DISK = 'K:\nosuchdisk.bak'

   BEGIN TRANSACTION

   INSERT testtbl(id, datecol) VALUES (11, '2001-01-01')

   INSERT testtbl(id, datecol)
      VALUES (12, '2002-02-02'), 
             ('13%', NULL), 
             (14, '2004-04-04')

   INSERT testtbl(id, datecol) VALUES (15, '2005-05-05')

   COMMIT TRANSACTION;
   PRINT '---inner_sp completed'
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
go
CREATE OR ALTER PROCEDURE outer_sp AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   PRINT 'outer_sp starting'
   EXEC inner_sp
   PRINT 'outer_sp completed'
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
 END CATCH
go
EXEC inner_sp
--EXEC outer_sp
go
SELECT id, datecol FROM testtbl ORDER BY id
IF @@trancount > 0 
BEGIN
   PRINT 'AFTER THE TEST THERE IS AN ACTIVE TRANSACTION!'
   ROLLBACK TRANSACTION
END
ELSE 
   PRINT 'No transaction is active after the test'
