May 14, 2009 at 1:19 am
I would like to know if I use a TRY..CATCH to call a stored proc, is it compulsory or rather advisable (best practice) to have a rollback statement in the CATCH section.
For example: I am calling an SP in a JOB STEP, and if the SP fails, I want it to notify me of failure (via mail). Here is the code:
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_name_here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
EXEC msdb.dbo.sp_send_dbmail
@recipients='email_address_here',
@subject = 'subject here',
@body = 'message here',
@body_format = 'HTML'
ROLLBACK TRANSACTION
END CATCH
Is the above good practice? Or is there a better way of doing this?
May 14, 2009 at 1:51 am
Hi,
I would think that it depends on the activity/process performed within the stored procedure.
For example, if you are performing some form of select statement, perhaps to generate a report, then there would be no data modifications to be rolled back, in which case it may not be required to wrap the stored procedure call in an explicit transaction.
You may be better off considering moving the transaction handling to within your stored procedure.
The opposite would be that if your stored procedure makes modifications to your database data, then the fact that the transaction could not complete correctly would most likely result in you wanting to rollback any intermediate modifications.
Hope this makes sense.
May 14, 2009 at 6:35 am
I agree, it depends.
A large majority of our applications have transaction control from the code, not within the procedures, so while we use TRY/CATCH error handling, we don't have a transaction rollback in the CATCH statement because the transaction itself is handled by the app code. In the places where we're handling transactions within TSQL, yeah, we put a ROLLBACK in the CATCH. But it really depends on what's happening with the code and the transactions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply