April 23, 2010 at 5:38 pm
The following query works as expected (rolls back everything) but I'm surpirsed. Don't I need to include a TRY..CATCH or a BEGIN TRANS in the stored proc myInsertProc?
TIA,
Barkingdog
CREATE TABLE [dbo].MyInsertTable
(
[myID] [int] NOT NULL PRIMARY KEY CLUSTERED
)
CREATE PROC myInsertProc
AS
INSERT MyInsertTable2 VALUES(1)
-- MyInsertTable is empty after this sql executes
SET XACT_ABORT On
BEGIN TRANSACTION ABC
BEGIN TRY
INSERT MyInsertTable VALUES(1)
INSERT MyInsertTable VALUES(2)
EXEC myInsert
commit TRANSACTION ABC
END Try
begin CATCH
IF (XACT_STATE()) = -1
-- open and uncommittable
ROLLBACK TRANSACTION ABC
ELSE IF (XACT_STATE()) = 1
-- open but committable
COMMIT TRANSACTION ABC
END CATCH
SET XACT_ABORT OFF
April 23, 2010 at 7:40 pm
Barkingdog (4/23/2010)
The following query works as expected (rolls back everything) but I'm surpirsed. Don't I need to include a TRY..CATCH or a BEGIN TRANS in the stored proc myInsertProc?TIA,
Barkingdog
CREATE TABLE [dbo].MyInsertTable
(
[myID] [int] NOT NULL PRIMARY KEY CLUSTERED
)
CREATE PROC myInsertProc
AS
INSERT MyInsertTable2 VALUES(1)
-- MyInsertTable is empty after this sql executes
SET XACT_ABORT On
BEGIN TRANSACTION ABC
BEGIN TRY
INSERT MyInsertTable VALUES(1)
INSERT MyInsertTable VALUES(2)
EXEC myInsert
commit TRANSACTION ABC
END Try
begin CATCH
IF (XACT_STATE()) = -1
-- open and uncommittable
ROLLBACK TRANSACTION ABC
ELSE IF (XACT_STATE()) = 1
-- open but committable
COMMIT TRANSACTION ABC
END CATCH
SET XACT_ABORT OFF
Actually, you don't need TRY/CATCH for any of that. Take it out of the above query and see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2010 at 2:03 am
Jeff,
You wrote "Actually, you don't need TRY/CATCH for any of that. Take it out of the above query and see what happens." I tried that and found that, indeed, I don't need the TRY\CATCH. But then I found this URL (see Examples section) where they use both TRY\CATCH and XACT_STATE.
http://msdn.microsoft.com/en-us/library/ms189797.aspx
Maybe the value of TRY\CATCH with XACT_STATE is it clearly shows the error-handling intent of the code.
TIA,
barkingdog
April 24, 2010 at 4:31 am
Barkingdog (4/24/2010)
Maybe the value of TRY\CATCH with XACT_STATE is it clearly shows the error-handling intent of the code.
The point of TRY...CATCH is to give you an opportunity to handle errors in a specific way - you might not always just want to roll everything back.
XACT_STATE is provided specifically for use with TRY...CATCH since an open transaction might be in an open but uncommittable state inside the CATCH block. XACT_STATE gives you the information you need to handle the situation correctly.
XACT_ABORT just ensures that (most) errors will abort any open transaction. This is not the default behaviour - many errors simply terminate the statement, but leave the transaction open.
Whether you choose to use XACT_ABORT, TRY...CATCH, or in-line error checking depends entirely on the circumstances.
Paul
April 24, 2010 at 7:49 am
The point being made was that the procedure itself never actually gets called, so there's no need for a try/catch within the proc. As far as I can tell - your query rolls back because you're firing something that doesn't even exist. (Exec MyInsert instead of Exec MyInsertProc)
But that said you do bring up a good point (which goes to what Paul is describing): there are time when you may need multiple levels of try catch, since some of the errors being thrown need to be caught by a TRY CATCh at a higher level than the error context.
So - there can be value in having TRY/Catch BOTH inside the proc and around the statement calling the proc.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 24, 2010 at 9:19 am
Barkingdog (4/24/2010)
Jeff,You wrote "Actually, you don't need TRY/CATCH for any of that. Take it out of the above query and see what happens." I tried that and found that, indeed, I don't need the TRY\CATCH. But then I found this URL (see Examples section) where they use both TRY\CATCH and XACT_STATE.
http://msdn.microsoft.com/en-us/library/ms189797.aspx
Maybe the value of TRY\CATCH with XACT_STATE is it clearly shows the error-handling intent of the code.
TIA,
barkingdog
My point was that if all you're going to do in the Try/Catch is a rollback, then there is no need for the Try/Catch. In the presence of SET XACT_ABORT ON, any runtime error will cause all of an explicit transaction to ROLLBACK auto-magically. From Books Online...
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, [font="Arial Black"]the entire transaction is terminated and rolled back[/font]. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
Shifting gears a bit, my belief is that Try/Catch is overused by a great many people. I believe that it should only be used for GUI related C.R.U.D. (Create, Retrieve, Update, Delete) where it's overused even there. Try/Catch is usually indicative of the fact that something went wrong with the data or the connection and I believe that the data and connection should have been verified long before any error has a chance to invoke Catch. In fact, my opinion is that Try/Catch is a form of spaghetti code that should usually be avoided in stored procedures, especially set based batch stored procedures, because it means that the programmer usually hasn't taken the time to prevalidate the data to ensure it's correctness before using it.
Yes, yes... just like Cursors and While Loops, there are exceptions where Try/Catch should probably exist even in batch code but, for the most part, Try/Catch is simply not necessary in batch code unless it's being used to make up for not actually pre-validating data correctly.
Heh... and yeah... I know what XACT_STATE does and that's why I try to avoid using it or Try/Catch. ROLLBACKs are far more expensive than doing it right the first time even if it's just for one row. That's why I make sure that my batch procs always "know" what they're doing with the data before they use the data instead of just throwing the data against the wall to see if it will stick. 😉
I can, indeed, see using Try/Catch for C.R.U.D. in a transactional environment as a final handler for concurrency issues. For example, two people entering the (relatively) same data very close to the same time may cause a key violation. Even then, my belief is that a proper final check as part of the transaction will be much more effective than letting Try/Catch do it for you.
If the code you posted is just to see what happens if Catch is invoked, then you might want to add some PRINT statments so you can easily tell.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2010 at 7:49 pm
Jeff Moden (4/24/2010)
In the presence of SET XACT_ABORT ON, any runtime error will cause all of an explicit transaction to ROLLBACK auto-magically. From Books Online...When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, [font="Arial Black"]the entire transaction is terminated and rolled back[/font]. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
Well, that's a very brave (and absolute) statement. I deliberately qualified my statement about XACT_ABORT because I'm not so sure it is bullet-proof...
http://www.sommarskog.se/error-handling-II.html#XACT_ABORT
About TRY...CATCH:
I do agree that it can be over-used (like anything really) but it is a vast improvement over checking @@ERROR. Retry logic for deadlocks, and savepoint rollbacks are all much easier with TRY CATCH. I do find TRY CATCH a useful construct compatible with good defensive programming practice.
Paul
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply