TRANS Split over 2 Stored Procs

[hegartyjp]

Hi,

Hoping someone could please help.

I have 2 stored procedures that run one after the other.

They both insert a row but in 2 seperate tables.

However the rows are linked so if the row goes in the first table, it has to also go in the second table.

I know how to use Transactions within a stored procedure but is there anyway to run one SP, then run the second but if the second one fails, roll back to before the first sp?

Any help greatly appreciated

Thanks

JP

 

[SQLDenis]

wrap the whole thing in a tran and use XACT_ABORT ON

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[hegartyjp]

Thanks.

But to wrap them in one TRAN do I need to have them all in one procedure?

Regards

JP

 

[SQLDenis]

play around with this

SET XACT_ABORT ON
BEGIN TRAN VooDoo
DECLARE @err int

EXEC @err = some_other_sp 
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 
BEGIN 
	ROLLBACK TRANSACTION  
END

EXEC @err = some__yet_another_sp 
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 
BEGIN 
	ROLLBACK TRANSACTION  
END

IF @err = 0 
BEGIN 
	COMMIT TRANSACTION  
END

also read:

http://www.sommarskog.se/error-handling-II.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[hegartyjp]

Thanks fella.

I will try this and see how I get on.

Appreciate the help

JP