Select rows changed in current transaction

Question

We have table name and CURRENT_TRANSACTION_ID().

How can we SELECT all rows INSERTed/UPDATEd in current transaction in MS SQL Server?

---

For example,

BEGIN TRANSACTION;
  EXEC some_big_procedure();
  --How to know what has been done inside some_big_procedure()?
ROLLBACK;

Note: original purpose is to test functions by comparing changes made by them with expected changes.

---

PostgreSQL has such mechanism (see this question):

BEGIN TRANSACTION;
  SELECT some_big_procedure();

  SELECT 'Changed row', t.*
  FROM my_table t
  WHERE t.xmin::text = (txid_current() % (2^32)::bigint)::text;
ROLLBACK;

Here txid_current() is analogue of CURRENT_TRANSACTION_ID(), t.xmin is built-in system column contains id of transaction which handles this row.

---

Bad solution is to write service function which will go through all the tables and add t.xmin's analogue. This column must be handled by triggers which are created dynamically by that service function too.

Any better idea for MS SQL?

Answer 1

You could return the rows from the proc via the OUTPUT clause.

insert into YourTable(Column1, Column2, Column3)
output inserted.Column1, inserted.Column2, inserted.Column3
--select... or values()... or what ever insert method

You would use the same methodology for DELETE and UPDATE. You can also store the OUTPUT into a table for further processing if need be.

Here is a DB Fiddle showing the below code

create table myTable (i int)
go

create proc myInsert
as
begin
   insert into myTable (i)
   output inserted.i
   values
   (1),(2),(3)
end
go

exec myInsert
go

create proc myDelete
as
begin

   create table #myTemp (i int)

   delete from myTable
   output deleted.* into #myTemp
   where i >= 2

   select * from #myTemp
end
go

exec myDelete
go