March 9, 2009 at 9:30 am
I have a number of SP's that I am reviewing and they all you TRY\CATCH exeption handling.
Howvere some use BEGIN TRAN\ROLLBACK TRAN as well. My question... Does a stroed procdure executing one or more unit of work IMPLICITLY create a transaction and hence meaning you do not need a BEGIN\ROLLBACK TRAN?
i.e what is correct
BEGIN TRY
--=========
INSERT
UPDATE
etc
--========
END TRY
--========
BEGIN CATCH
--========
or
BEGIN TRAN
BEGIN TRY
--=========
INSERT
UPDATE
etc
--========
COMMIT TRANSACTION
END TRY
--========
BEGIN CATCH
--========
ROLLBACK TRAN
March 9, 2009 at 9:34 am
A stored procedure does NOT implicitly create a transaction, so your second statement sounds like the way to go. If you want to be within a transaction for that case, then you MUST state an explicit begin transaction, and then Commit or rollback at the end.
Beware that the Stored Procedure will check the transaction count when entering and exiting your stored procedure, and will return an error if it is not the same (i.e. you forget to commit or rollback a transaction, or you rollback a transaction that was started in another SP).
Hope that helps,
Cheers,
J-F
March 9, 2009 at 9:40 am
excellent, thanks for your speedy response
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply