2

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?

0

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
  • In this case we need to modify some_big_procedure(). And what if we need to test output from 100 big procedures? Sorry, solution from question will be faster to implement. – Evgeny Nozdrev Oct 17 '18 at 9:55
  • Yes it will be fast to implement. This is almost the only way to do it – scsimon Oct 17 '18 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.