Retaining table values in spite of Rollback

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retaining table values in spite of Rollback

Author

Topic

Girish_kamalapur
Starting Member

3 Posts

Posted - 2011-02-18 : 09:11:14

Hi All,

I am in strange situation where I need help.
I have a stored procedure which has defined with Commit / Rollback transaction. within this block, there is a call to a child stored procedure. Inside the child stored procedure I am populating a table with complex manipulation as result of other stored procedures, and inserting the result set to one table where it is giving the Primary key violation error. Note: I have a delete statement where it deletes the matching records from destination table before insert.

Flow:
Parent SP
Child SP A
Child SP B
Child SP C (Errors and rollback all data)
Statements of Parent
Commit / Rollback

If I take out the commit rollback half of the values are committed to the table and gives error, but second run will succeed because of half results of first run so I cannot retest the case.

I came across use of Table variables which are beyond transaction. But when I pass the table variable to child procedure, it becomes read only where I cannot populate the values.
If I define the table variable in child procedure it is not visible to parent.

How I can retain a table values in spite of rollback?
Is there any way by which I can isolate a table from the transaction and retain its values?.

I have even tried with SQL profiler which is not helpful in identifying the error at row level.

Thank you in advance.
Girish

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-18 : 09:34:22

This is a kludgy way to do it, but you you use table variables exclusively for all of the transactional work you want to preserve, and then insert that data into a temp table just before calling a child procedure. The child procedure then declares its own table variable and immediately inserts the results of the temp table into the variable, then truncates the temp table. Here's a template:

create procedure parent as
create table #abc (...)
declare @xyz table(...)

insert into @xyz select * from data_source

begin tran
--... all code does work on @xyz
commit tran

insert into #abc select * from @xyz
exec child_procA
go

create procedure child_procA as
declare @xyz table(...)

insert into @xyz select * from #abc
truncate table #abc

begin tran
--... all code does work on @xyz
commit tran

insert into #abc select * from @xyz
exec child_procB
go

create procedure child_procB as
declare @xyz table(...)

insert into @xyz select * from #abc
truncate table #abc

begin tran
--... all code does work on @xyz
commit tran

insert into #abc select * from @xyz
exec child_procC
go

-- all remaining child procedures are structured similarly

The important things to remember are to clear the temp table after inserting to the variable, and NEVER do any temp table operations inside a transaction. That way a rollback won't undo the changes, and the temp table is only used for passing data between procedures. When the last child procedure runs it will populate the final results in the temp table, and the parent can proceed normally.

Girish_kamalapur
Starting Member

3 Posts

Posted - 2011-02-18 : 09:48:08

Hi Rob,
Thanks for quick reply.
I came across that when we pass the table variables as parameter we cannot insert into. They become read only.
The table variable is populated in child procedure. It cannot access the table variable defined in parent.
I cannot "insert into @xyz select * from #abc" under child. Please suggest.
Regards,
Girish

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-18 : 10:30:00

quote:
I came across that when we pass the table variables as parameter we cannot insert into. They become read only.

Please re-read my post. The technique I describe does not pass table variables as parameters. (in fact you don't need any parameters for this)

quote:
The table variable is populated in child procedure. It cannot access the table variable defined in parent.

I understand that, that's what the temp table is for, to bridge data access between stored procedures.

quote:
I cannot "insert into @xyz select * from #abc" under child. Please suggest.

I'd need to see more code to understand why that doesn't work.

Think of it this way: A teacher puts a math problem on a chalkboard/whiteboard for the class. You copy the notes to paper, work on them, and them erase the board and write your work on it. The next student copies those notes from the board, revises them on paper, then erases the board and copies their notes to it. The process continues until all students have finished and the teacher reads the result. The temp table is the chalkboard, the table variable acts as a student's notebook.

Girish_kamalapur
Starting Member

3 Posts

Posted - 2011-02-18 : 11:13:01

You meant not to drop the temp table at child procedure and pull the data to table variable at parent level just before rollback. Please correct me if I am wrong.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-18 : 13:06:52

Correct, do not DROP the temp table, just TRUNCATE its data inside each child procedure immediately after transferring it to the table variable. Then INSERT into the temp table from the table variable just before you call the next procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources