February 21, 2010 at 9:03 pm
I am writing some Stored Procs and was wondering which syntax is better? Option 1 or Option 2? Or do they do the same thing?
Option 1) - BEGIN TRANSACTION comes before BEGIN TRY?
BEGIN TRANSACTION
BEGIN TRY
UPDATE MyChecking SET Amount = Amount - $90.00
WHERE AccountNum = 12345
UPDATE MySavings SET Amount = Amount + $990.00
WHERE AccountNum = 12345
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR 50001 'Transaction'
ROLLBACK TRANSACTION
END CATCH
GO
or;
Option 2) - BEGIN TRY comes before BEGIN TRANSACTION?
ALTER PROC usp_AccountTransaction
@AccountNum INT,
@Amount DECIMAL
AS
BEGIN
BEGIN TRY --Start the Try Block..
BEGIN TRANSACTION -- Start the transaction..
UPDATE MyChecking SET Amount = Amount - @Amount
WHERE AccountNum = @AccountNum
UPDATE MySavings SET Amount = Amount + @Amount
WHERE AccountNum = @AccountNum
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
END
GO
February 21, 2010 at 9:13 pm
As far as I know - option 1 is not "legal". You should get something squeeking at you for putting the BEGIN tran outside of the TRY, and the commit INSIDE the TRY.
That said - the second one usually makes most sense. Since you may not always know what will cause the transaction to fail, you would want the rollback (and possibly commit) logic available in the CATCH, so you'd want to put the BEGIN TRAN inside of the TRY.
----------------------------------------------------------------------------------
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?
February 21, 2010 at 9:48 pm
Thanks. That's what i was thinking as well. Apologies i didn't run some tests in SSMS first.
I thought Option 2 was the better format and made the most logical sense for error trapping and commit/rollback.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply