How can I ensure that nested transactions are committed independently of each other?

Posted by Caldera on Stack Overflow See other posts from Stack Overflow or by Caldera
Published on 2011-01-06T12:35:20Z Indexed on 2011/01/06 12:54 UTC
Read the original article Hit count: 162

If I have a stored procedure that executes another stored procedure several times with different arguments, is it possible to have each of these calls commit independently of the others?

In other words, if the first two executions of the nested procedure succeed, but the third one fails, is it possible to preserve the results of the first two executions (and not roll them back)?

I have a stored procedure defined something like this in SQL Server 2000:

CREATE PROCEDURE toplevel_proc ..
AS
BEGIN

         ...

         while @row_count <= @max_rows
    begin
        select @parameter ... where rownum = @row_count 
        exec nested_proc @parameter
        select @row_count = @row_count + 1
    end

END

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about tsql