How can I ensure that nested transactions are committed independently of each other?
- by Caldera
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
