December 1, 2003 at 3:14 pm
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 int
SELECT @ID = ID FROM MyTable WHERE blah, blah, blah
SET @Count = @@ROWCOUNT
IF @Count = 0
BEGIN
SET @ResultMessage = 'There was no match'
SET @Confirmation = 1
END
ELSE --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'
END
on_error:
SET @Confirmation = 1
SET @ResultMessage = 'Failed and was rolled back'
GO
This always runs the on_error label code. I have the followinvnng solution:
DECLARE
@Count int,
@ID int
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
IF @@ERROR <> 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:
SET @Confirmation = 0
SET @ResultMessage = 'Committed successfully'
GO
What I need to know is whether or not it is within good stored procedure coding practices. Thanks in advance.
Edited by - DerPflug on 12/01/2003 3:15:05 PM
Edited by - DerPflug on 12/01/2003 3:16:27 PM
Edited by - DerPflug on 12/01/2003 3:27:00 PM
Edited by - DerPflug on 12/01/2003 3:27:50 PM
December 1, 2003 at 8:21 pm
couple of suggestions:
First your code:
quote:
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 BEGINSET @ResultMessage = 'There was no match in the database' SET @Confirmation = 1 ENDELSE --if it exists update fields BEGINBEGIN TRANUPDATE AnotherTable SET this = that WHERE ID = @IDIF @@ERROR <> 0 --if transaction fails rollback transaction BEGINROLLBACK TRANGOTO on_error ENDCOMMIT TRANGOTO proc_end ENDon_error: SET @Confirmation = 1SET @ResultMessage = 'Failed and was rolled back'proc_end:SET @Confirmation = 0SET @ResultMessage = 'Committed successfully'GO
should have at least 2 RETURN statements to be correct from the work flow perspective.
Second. To separate the Update from the ID selection will risk the fact that another session may delete the Id between your select and the update
I would do it like
UPDATE AnotherTable SET This = That WHERE bla bla bla
select @Cnt = @@RowCount, @Err = @@ERROR
IF @ERR <> 0 ...
IF @Cnt = 0 ...
That way you don't Need to declare transactions for single statements and you are protected against the double check problem
HTH
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply