Stored Procedures and transactions

1
-1
-1

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 IMMEDIATE 'BEGIN TRANSACTION' USING THIS;
EXECUTE my_sproc;
COMMIT;
 
My question is will this work the way I want it to? The SQL Server documentation seems to say it will.
 

Question Tags: 

Answers

Narayana Bhat answered Stored Procedures and transactions

1
0
-1
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

mtrainor@skibocorp.com'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.