IMPLICIT_TRANSACTIONS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IMPLICIT_TRANSACTIONS

Author

Topic

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-08-05 : 04:18:17

Hi All,

I have such questions. I am trying to use Explicit transactions with IMPLICIT_TRANSACTIONS ON and I should design error handling about that. The situation is like that:

CREATE PROCEDURE X
AS
SET IMPLICIT_TRANSACTIONS ON
DECLARE @err_var int, @err_des varchar(128), @level varchar(50)

BEGIN TRAN MAIN
UPDATE BATCH1

SELECT @err_var=@@ERROR, @level='lev_UPD1'
IF @err_var<>0 GOTO ERR
ELSE COMMIT TRAN

UPDATE BATCH2

SELECT @err_var=@@ERROR, @level='lev_UPD2'
IF @err_var<>0 GOTO ERR
ELSE COMMIT TRAN

...

COMMIT TRAN MAIN

ERR:
IF @err_var<>0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN MAIN
SELECT @err_des=
CASE
WHEN @level='lev_MARK1' THEN X1
WHEN @level='lev_MARK1' THEN X2
...
END
ELSE
BEGIN
COMMIT TRAN MAIN
END

Is it a correct way or I am too far from the truth?
Thanks in advance.

The Rebel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources