Stored Procedures and transactions


My app is running on SQL Server 2016 and PB 12.5. AutoCommit is set to False.

I want to run a stored procedure that does some updates so I need to do a commit after. I also want any updates that were pending before the stored procedure was called to remain pending until I commit or rollback.

My understanding is that I can do the following to isolate the updates in the stored procedure from the current transaction.

EXECUTE my_sproc;
My question is will this work the way I want it to? The SQL Server documentation seems to say it will.

Question Tags: 


Narayana Bhat answered Stored Procedures and transactions

Dear Roland, Same feature exist in SQL Anywhere & Enterprises and it is working. I am not aware much about SQL Server It is almost same as Savepoint Narayana's picture

Hi Roland, you could create a data window that calls the stored procedure. Update the columns, then simply call Update() and Commit. I did this last year fro the company I previously worked for when they needed the Freight Rate Shopping system I had written to update a new Inventory Management system.Worked like a charm.