I want to Commit the a number of transactions, generated in one stored procedure, from another procedure.

Is this posible?

I think you can do this with named transactions. Perhaps you can store the names of open transactions in a table from the sp that begins them, then read and commit them by name from the second sp. So something like this:

DECLARE @tran_name VARCHAR(30)

SET @tran_name='test1'

INSERT INTO my_transactions
SELECT @tran_name

BEGIN TRANSACTION @tran_name

.
.
.


then in other sp:

DECLARE @tran_name VARCHAR(30)

SELECT @tran_name = transaction_name from my_transactions
COMMIT TRANSACTION @tran_name
DELETE FROM my_transactions WHERE transaction_name = @tran_name


No idea if this will work, but I'd be interested in what you come up with.

The problem is that the COMMIT TRANSACTION needs at BEGIN TRANSACTION, or els an error occurs.

My suggestion is no good, as even named transations will go out of scope when the first sp ends.

I am very curious as to why you would need to do this.

Keep in mind that as long as the transaction is open, you will be locking resources in the database.

I have a program that logs events in to a table and I only want the logging to occure when 500 events is checked and alright.

The program have to use a stores procedure for the logging and one for the COMMIT.