October 16, 2013 at 11:14 am
I have the following rollback code in cascaded sp does this work?
CREATE Procedure SP1
AS
BEGIN TRAN TRAN1
BEGIN TRY
Update Table1
exec childsp1
END TRY
BEGIN CATCH
print 'error rollback'
ROLLBACK TRANSACTION TRAN1
END CATCH
COMMIT TRNSACTION TRAN1
GO
--------------------------------
CREATE PROCEDURE childSP1
AS
BEGIN TRAN TRANA
BEGIN TRY
Update Table2
END TRY
BEGIN CATCH
print 'error rollback'
ROLLBACK TRANSACTION TRANA
END CATCH
COMMIT TRNSACTION TRANA
GO
Thank you for I/p
October 16, 2013 at 12:03 pm
Guras (10/16/2013)
I have the following rollback code in cascaded sp does this work?CREATE Procedure SP1
AS
BEGIN TRAN TRAN1
BEGIN TRY
Update Table1
exec childsp1
END TRY
BEGIN CATCH
print 'error rollback'
ROLLBACK TRANSACTION TRAN1
END CATCH
COMMIT TRNSACTION TRAN1
GO
--------------------------------
CREATE PROCEDURE childSP1
AS
BEGIN TRAN TRANA
BEGIN TRY
Update Table2
END TRY
BEGIN CATCH
print 'error rollback'
ROLLBACK TRANSACTION TRANA
END CATCH
COMMIT TRNSACTION TRANA
GO
Thank you for I/p
Well it will work until you encounter an error. Your format is a bit off. It should be something like this.
begin try
begin transaction
--do stuff
commit transaction
end try
begin catch
rollback transaction
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply