Alberto (il****@libero.it) writes:
I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.
I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.
But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.
Is there a way to force a commit durint a sp ?
WHILE @@trancount > 1
COMMIT TRANSACTION
But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.
A much better approach is to add to the beginning of the procedure:
IF @@trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END
That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp