try...catch and transaction coding style

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 2005 Forums

Transact-SQL (2005)

try...catch and transaction coding style

Author

Topic

Joker
Starting Member

1 Post

Posted - 2010-03-24 : 04:31:40

Hi

Am trying to code correct and are wondering which of these code blocks are
the most right.

Thanks

--Version 1:
BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM dbo.Address WHERE UserId = @userid;

        IF(@@ROWCOUNT = 0)
            BEGIN
                IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
                SET @statuscode = 20;	
                RETURN;
            END

            SET @statuscode = 100;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF(@@TRANCOUNT > 0)ROLLBACK TRANSACTION;
    EXEC dbo.Error_ErrorHandler;
    SET @statuscode = 99;
END CATCH


--Version 2:
BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON

        DELETE FROM dbo.Address WHERE UserId = @userid;

        IF(@@ROWCOUNT = 0)
            BEGIN
                IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
                SET @statuscode = 20;	
                RETURN;
            END

        SET @statuscode = 100;
END TRY
BEGIN CATCH
    IF (XACT_STATE() = -1)
        ROLLBACK TRANSACTION;
    ELSE IF (XACT_STATE() = 1)
        COMMIT TRANSACTION; 

    EXEC dbo.Error_ErrorHandler;
    SET @statuscode = 99;
END CATCH


--Version 3:
BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRANSACTION
        DELETE FROM dbo.Address WHERE UserId = @userid;

        IF(@@ROWCOUNT = 0)
            BEGIN
                IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
                SET @statuscode = 20;	
                RETURN;
            END

        SET @statuscode = 100;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION;    
    EXEC dbo.Error_ErrorHandler;
    SET @statuscode = 99;
END CATCH


--Version 4:
BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRANSACTION
        DELETE FROM dbo.Address WHERE UserId = @userid;

        IF(@@ROWCOUNT = 0)
            BEGIN
                IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
                SET @statuscode = 20;	
                RETURN;
            END

        SET @statuscode = 100;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF (XACT_STATE() = -1)
        ROLLBACK TRANSACTION;
    ELSE IF (XACT_STATE() = 1)
        COMMIT TRANSACTION; 

    EXEC dbo.Error_ErrorHandler;
    SET @statuscode = 99;
END CATCH

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 06:37:26

I'll be interested in answers too.

Here's how I do a TRANSACTION that I want to ROLLBACK without impacting anyone that called me (I also return an error code so the caller can Rollback their changes too, if they need to.


BEGIN TRANSACTION MyLabel_01
SAVE  TRANSACTION MyLabel_02

... some code ...

IF ... test for No Errors ...
BEGIN
	COMMIT TRANSACTION MyLabel_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MyLabel_02
	COMMIT TRANSACTION MyLabel_01
END

We put


SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

at the top of all our SProcs (After AS and before the first statement in the Sproc)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-24 : 07:32:32

Definitely not version 2, which appears to be missing a BEGIN TRANSACTION. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-24 : 08:13:18

Similar to Kristen's


DECLARE @tranCounter INT               SET @tranCounter = @@TRANCOUNT

-- Set up a transaction so we can undo if anything goes wrong!
-- If we are inside a transaction save the transaction so we can
-- rollback only to this point. If we aren't in a transaction create one
IF @tranCounter > 0 SAVE TRANSACTION addAdminUser
ELSE BEGIN TRANSACTION AddAdminUser

< STUFF >

-- Do something that can result in an error and test for it.
IF @error <> 0 GOTO failWithError

< STUFF >

-- If we get to this point then all is good. Commit and exit
-- If in a nested transaction just save the changes to the transaction
-- If inside our own transaction then commit that transaction
IF @tranCounter > 0 SAVE TRANSACTION AddAdminUser
ELSE COMMIT TRANSACTION AddAdminUser
RETURN

-- If we ever get here then something has gone wrong!
-- rollback the saved transaction and exit as gracefully as possible!
failWithError:
SET @msgText = ' ! ERROR            : Error Occurred, return code ('
	+ CAST(@error AS VARCHAR(10))
	+ ')'

RAISERROR(@msgText, 11, 11) WITH NOWAIT
ROLLBACK TRANSACTION AddAdminUser
RETURN @error

Depending on the stored proc then parts will be in TRY CATCH blocks and there won't be RAISERROR messages.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 12:58:15

TC does that work when you ROLLBACK TRANSACTION AddAdminUser?

I seemed to think that in my experiments any ROLLBACK caused the outer callers to have problems, whereas if we COMMITTED, and then returned, it was fine - hence we created a dummy "wrapper transaction", then a named inner transaction within that, and we either Committed or Rolled back the inner transaction, but we always Committed the "wrapper transaction" which left us in a "correct state for the outer caller

Years since we built that logic into our systems, so I now can't remember what the experimentation was at the time!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-24 : 13:06:51

It seems to work anyway -- I tested it fairly hard.

The only way the ROLLBACK can happen is if there has been a problem and it will either ROLLBACK a transaction started by the stored proc (if it was called outside a TRANSACTIOn) or a save point. The following example script works for me!


BEGIN TRANSACTION

CREATE TABLE #foo ([val] VARCHAR(15))
INSERT #foo SELECT 'ALIVE!'

SELECT @@TRANCOUNT

DECLARE @tranCounter INT
SET @tranCounter = @@TRANCOUNT

IF @tranCounter > 0 SAVE TRANSACTION foo

INSERT #foo SELECT 'I''LL DIE!!!'
SELECT @@TRANCOUNT

SELECT * FROM #foo
ROLLBACK TRANSACTION foo

SELECT @@TRANCOUNT
SELECT * FROM #foo
ROLLBACK TRANSACTION

SELECT @@TRANCOUNT

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:17:35

Indeed, seems fine


CREATE PROCEDURE TranTest02
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@tranCounter INT,
	@error INT,
	@msgText NVarchar(4000)

PRINT 'TranTest02 Start'

	SELECT	@tranCounter = @@TRANCOUNT,
		@error = 0	-- Assume no error
	
	-- Set up a transaction so we can undo if anything goes wrong!
	-- If we are inside a transaction save the transaction so we can
	-- rollback only to this point. If we aren't in a transaction create one
	IF @tranCounter > 0 SAVE TRANSACTION addAdminUser
	ELSE BEGIN TRANSACTION AddAdminUser
	
	-- Do STUFF here, but for this test we will force an error:
	IF @TheReturnValue <> 0
	BEGIN
		SET @error = 1
	END
	
	-- Do something that can result in an error and test for it.
	IF @error <> 0 GOTO failWithError
	
	-- Do more STUFF here - this is unrechable in this test
	
	-- If we get to this point then all is good. Commit and exit
	-- If in a nested transaction just save the changes to the transaction
	-- If inside our own transaction then commit that transaction
	IF @tranCounter > 0 
	BEGIN
		SAVE TRANSACTION AddAdminUser
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION AddAdminUser
	END
PRINT 'TranTest02 RETURN 0'
	RETURN 0
	
	-- If we ever get here then something has gone wrong!
	-- rollback the saved transaction and exit as gracefully as possible!
failWithError:
	SET @msgText = ' ! ERROR            : Error Occurred, return code ('
		+ CAST(@error AS VARCHAR(10))
		+ ')'
	
	RAISERROR(@msgText, 11, 11) WITH NOWAIT
	ROLLBACK TRANSACTION AddAdminUser
PRINT 'TranTest02 RETURN ERROR'
	RETURN @error
GO

CREATE PROCEDURE TranTest01
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT
PRINT 'TranTest01 Start'
	BEGIN TRANSACTION

	EXEC @error = TranTest02 @TheReturnValue = @TheReturnValue

	COMMIT
PRINT 'TranTest01 End'
	RETURN @error
GO

DECLARE	@intResult int
SET NOCOUNT ON
PRINT 'Test OK Start'
EXEC @intResult = TranTest01 @TheReturnValue = 0
SELECT [@intResult] = @intResult
PRINT 'Test OK End'
SET NOCOUNT OFF

SET NOCOUNT ON
PRINT 'Test Fail Start'
EXEC @intResult = TranTest01 @TheReturnValue = 1
SELECT [@intResult] = @intResult
PRINT 'Test Fail End'
SET NOCOUNT OFF

DROP PROCEDURE TranTest02
GO
DROP PROCEDURE TranTest01
GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-24 : 13:20:13

Maybe it was a quirk in the way 2000 handled transactions?

I've not got a 2000 box around any more to test on. I implemented this framework only after we moved to 2005 in an effort to clean up the error handling code a little.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:25:16

Kristen way:


CREATE PROCEDURE TranTest04
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT,
	@msgText NVarchar(4000)

PRINT 'TranTest04 Start'

	SELECT	@error = 0	-- Assume no error
	
	BEGIN TRANSACTION MyLabel_01
	SAVE  TRANSACTION MyLabel_02

	-- Do STUFF here, but for this test we will force an error:
	IF @TheReturnValue <> 0
	BEGIN
		SET @error = 1
	END
	
	-- Do something that can result in an error and test for it.
	IF @error <> 0 GOTO failWithError
	
	-- Do more STUFF here - this is unrechable in this test
	
failWithError:
	IF @error = 0
	BEGIN
		-- No error
		COMMIT TRANSACTION MyLabel_01
	END
	ELSE
	BEGIN
		-- Error occurred
		SET @msgText = ' ! ERROR            : Error Occurred, return code ('
			+ CAST(@error AS VARCHAR(10))
			+ ')'
		
		RAISERROR(@msgText, 11, 11) WITH NOWAIT
		ROLLBACK TRANSACTION MyLabel_02
		COMMIT TRANSACTION MyLabel_01
	END
PRINT 'TranTest04 RETURN 0'
	RETURN @error
GO

CREATE PROCEDURE TranTest03
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT
PRINT 'TranTest03 Start'
	BEGIN TRANSACTION

	EXEC @error = TranTest04 @TheReturnValue = @TheReturnValue

	COMMIT
PRINT 'TranTest03 End'
	RETURN @error
GO

DECLARE	@intResult int
SET NOCOUNT ON
PRINT 'Test OK Start'
EXEC @intResult = TranTest03 @TheReturnValue = 0
SELECT [@intResult] = @intResult
PRINT 'Test OK End'
SET NOCOUNT OFF

SET NOCOUNT ON
PRINT 'Test Fail Start'
EXEC @intResult = TranTest03 @TheReturnValue = 1
SELECT [@intResult] = @intResult
PRINT 'Test Fail End'
SET NOCOUNT OFF

DROP PROCEDURE TranTest04
GO
DROP PROCEDURE TranTest03
GO

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:28:24

and the "simple" way with no SAVE POINT (which gives run-time error)


CREATE PROCEDURE TranTest06
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT,
	@msgText NVarchar(4000)

PRINT 'TranTest06 Start'

	SELECT	@error = 0	-- Assume no error
	
	BEGIN TRANSACTION

	-- Do STUFF here, but for this test we will force an error:
	IF @TheReturnValue <> 0
	BEGIN
		SET @error = 1
	END
	
	-- Do something that can result in an error and test for it.
	IF @error <> 0 GOTO failWithError
	
	-- Do more STUFF here - this is unrechable in this test
	
failWithError:
	IF @error = 0
	BEGIN
		-- No error
		COMMIT TRANSACTION
	END
	ELSE
	BEGIN
		-- Error occurred
		SET @msgText = ' ! ERROR            : Error Occurred, return code ('
			+ CAST(@error AS VARCHAR(10))
			+ ')'
		
		RAISERROR(@msgText, 11, 11) WITH NOWAIT
		ROLLBACK TRANSACTION
	END
PRINT 'TranTest06 RETURN 0'
	RETURN @error
GO

CREATE PROCEDURE TranTest05
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT
PRINT 'TranTest05 Start'
	BEGIN TRANSACTION

	EXEC @error = TranTest06 @TheReturnValue = @TheReturnValue

	COMMIT
PRINT 'TranTest05 End'
	RETURN @error
GO

DECLARE	@intResult int
SET NOCOUNT ON
PRINT 'Test OK Start'
EXEC @intResult = TranTest05 @TheReturnValue = 0
SELECT [@intResult] = @intResult
PRINT 'Test OK End'
SET NOCOUNT OFF

SET NOCOUNT ON
PRINT 'Test Fail Start'
EXEC @intResult = TranTest05 @TheReturnValue = 1
SELECT [@intResult] = @intResult
PRINT 'Test Fail End'
SET NOCOUNT OFF

DROP PROCEDURE TranTest06
GO
DROP PROCEDURE TranTest05
GO

Test OK Start
TranTest05 Start
TranTest06 Start
TranTest06 RETURN 0
TranTest05 End
@intResult  
----------- 
0

Test OK End
Test Fail Start
Server: Msg 50000, Level 11, State 11, Procedure TranTest06, Line 37
 ! ERROR            : Error Occurred, return code (1)
TranTest05 Start
TranTest06 Start
Server: Msg 266, Level 16, State 2, Procedure TranTest06, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. 
Previous count = 1, current count = 0.
Server: Msg 3902, Level 16, State 1, Procedure TranTest05, Line 11
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
TranTest06 RETURN 0
TranTest05 End
@intResult  
----------- 
1

Test Fail End

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-24 : 13:40:56

Kristen! Don't do that to me at half five! Thought my way was broken from your script but it still works!



CREATE PROCEDURE TranTest06
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT,
	@msgText NVarchar(4000)

PRINT 'TranTest06 Start'

	SELECT	@error = 0	-- Assume no error
	
	DECLARE @transactions INT
	SET @transactions = @@TRANCOUNT

	IF @transactions > 0 SAVE TRANSACTION foo
	ELSE BEGIN TRANSACTION 

	-- Do STUFF here, but for this test we will force an error:
	IF @TheReturnValue <> 0
	BEGIN
		SET @error = 1
	END
	
	-- Do something that can result in an error and test for it.
	IF @error <> 0 GOTO failWithError
	
	-- Do more STUFF here - this is unrechable in this test
	
failWithError:
	IF @error = 0
	BEGIN
		-- No error
		IF @transactions > 0 SAVE TRANSACTION foo
		ELSE COMMIT TRANSACTION
	END
	ELSE
	BEGIN
		-- Error occurred
		SET @msgText = ' ! ERROR            : Error Occurred, return code ('
			+ CAST(@error AS VARCHAR(10))
			+ ')'
		
		RAISERROR(@msgText, 11, 11) WITH NOWAIT
		SELECT @@TRANCOUNT
		IF @transactions > 0 ROLLBACK TRANSACTION foo
		ELSE ROLLBACK TRANSACTION
	END
PRINT 'TranTest06 RETURN 0'
	RETURN @error
GO

CREATE PROCEDURE TranTest05
	@TheReturnValue	int	-- 1=Force error, 0=No error
AS
DECLARE	@error INT
PRINT 'TranTest05 Start'
	BEGIN TRANSACTION

	SELECT @@TRANCOUNT
	EXEC @error = TranTest06 @TheReturnValue = @TheReturnValue

	COMMIT
PRINT 'TranTest05 End'
	RETURN @error
GO

--MAIN BODY
DECLARE	@intResult int
SET NOCOUNT ON
PRINT 'Test OK Start'
EXEC @intResult = TranTest05 @TheReturnValue = 0
SELECT [@intResult] = @intResult
PRINT 'Test OK End'
SET NOCOUNT OFF

SET NOCOUNT ON
PRINT 'Test Fail Start'
EXEC @intResult = TranTest05 @TheReturnValue = 1
SELECT [@intResult] = @intResult
PRINT 'Test Fail End'
SET NOCOUNT OFF

DROP PROCEDURE TranTest06
GO
DROP PROCEDURE TranTest05
GO

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:43:24

" Thought my way was broken from your script but it still works!"

I thought your way was broken because I got an error message - then realised it was your raiserror() !!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-24 : 14:47:08

lol

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-24 : 15:13:10

quote:
Originally posted by DBA in the making

Definitely not version 2, which appears to be missing a BEGIN TRANSACTION. :)

Not to get to picky, just informational.. None of the transactions are needed (in the OP first post) since the single delete is atomic and thus already in a implicit transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources