Transactions and error checking

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)

Transactions and error checking

Author

Topic

DerPflug
Starting Member

1 Post

Posted - 2003-12-01 : 17:31:25

I'm using transactions for the first time in the context of a stored procedure and I have a question. Using the following code:


DECLARE	@Count int,	@ID intSELECT @ID = ID FROM MyTable WHERE blah, blah, blahSET @Count = @@ROWCOUNTIF @Count = 0   BEGIN	SET @ResultMessage = 'There was no match' 	SET @Confirmation = 1  ENDELSE  --if it exists update fields  BEGIN	BEGIN TRAN	UPDATE AnotherTable SET This = That WHERE ID = @ID	IF @@ERROR <> 0 --if transaction fails rollback transaction	  BEGIN		ROLLBACK TRAN		GOTO on_error	  END	COMMIT TRAN	SET @Confirmation = 0        SET @ResultMessage = 'Committed    successfully'  ENDon_error:  SET @Confirmation = 1SET @ResultMessage = 'Failed and was rolled back'GO 
GO

This always runs the on_error label code. I have the following solution:


DECLARE	@Count int,	@ID intSELECT @ID = ID FROM MyTable WHERE blah, blah, blah SET @Count = @@ROWCOUNTIF @Count = 0 --if does not exist send back error code  BEGIN	SET @ResultMessage = 'There was no match in the database' 	SET @Confirmation = 1  ENDELSE  --if it exists update fields  BEGIN	BEGIN TRAN	UPDATE AnotherTable SET this = that WHERE ID = @ID	IF @@ERROR <> 0 --if transaction fails rollback transaction	  BEGIN		ROLLBACK TRAN		GOTO on_error	  END	COMMIT TRAN	GOTO proc_end  ENDon_error:  SET @Confirmation = 1SET @ResultMessage = 'Failed and was rolled back'proc_end:SET @Confirmation = 0SET @ResultMessage = 'Committed successfully'GO

What I need to know is whether or not it is within good stored procedure coding practices. Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 17:35:07

The code did not copy well into the thread (all code on one line so can't read it). Put code tags around the TSQL: [ c o d e] and [ / c o d e ] without the spaces so that we can see it better.

If your question is about labels, I have never used labels in any stored procedures as it was drilled into my brain that labels are considered bad programming. I have seen lots of people use them. I think that they are misused though.

Tara

Granick
Starting Member

46 Posts

Posted - 2003-12-01 : 17:42:28

The solution you have below will have the opposite problem that the upper setup has. It will Always run the proc_end section, so it will always set the @Confirmation = 0 etc.

A simple solution to this would be to place use a variable to track current Error status, and check that in the proc_end to know if you should do that, as follows:

DECLARE @Count int, @ID int
DECLARE @ErrorNum int
SET @ErrorNum = 0 -- Default the value to ZERO

SELECT @ID = ID FROM MyTable WHERE blah, blah, blah
SET @Count = @@ROWCOUNT
IF @Count = 0 --if does not exist send back error code
BEGIN
SET @ResultMessage = 'There was no match in the database'
SET @Confirmation = 1
END
ELSE --if it exists update fields
BEGIN
BEGIN TRAN
UPDATE AnotherTable
SET this = that
WHERE ID = @ID
SET @ErrorNum = @@ERROR -- Set the Value here, so we can check it later
IF @ErrorNum <> 0 --if transaction fails rollback transaction
BEGIN
ROLLBACK TRAN
GOTO on_error
END
COMMIT TRAN
GOTO proc_end
END
on_error: SET @Confirmation = 1
SET @ResultMessage = 'Failed and was rolled back'

proc_end:
IF(@ErrorNum = 0)
BEGIN
SET @Confirmation = 0
SET @ResultMessage = 'Committed successfully'
END

There are many ways you can do this, but this should work for you, without drastic changes to what you already have.

Shannon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources