Best way for Atomic testing in SP

[dee-u]

This is how I am currently doing it, I am checking both @@ERROR and @@ROWCOUNT to determine if an action query failed or not. Is this the best and most reliable way of doing such?

DECLARE @errorvar1 int, @rowcountvar1 int
DECLARE @errorvar2 int, @rowcountvar2 int
DECLARE @errorvar3 int, @rowcountvar3 int
DECLARE @errorvar4 int, @rowcountvar4 int

--Start transaction
BEGIN TRANSACTION
INSERT INTO ...
--Log error and rows affected
SELECT @errorvar1 = @@ERROR, @rowcountvar1 = @@ROWCOUNT

UPDATE Table ...
--Log error and rows affected
    SELECT @errorvar2 = @@ERROR, @rowcountvar2 = @@ROWCOUNT

INSERT INTO ...
--Log error and rows affected
SELECT @errorvar3 = @@ERROR, @rowcountvar3 = @@ROWCOUNT

UPDATE TABLE ...
--Log error and rows affected
SELECT @errorvar4 = @@ERROR, @rowcountvar4 = @@ROWCOUNT

IF
    @errorvar1 = 0 AND @rowcountvar1 >= 1 AND
    @errorvar2 = 0 AND @rowcountvar2 >= 1 AND
    @errorvar3 = 0 AND @rowcountvar3 >= 1 AND
    @errorvar4 = 0 AND @rowcountvar4 >= 1
    BEGIN
        COMMIT TRANSACTION
        RETURN 1
    End
Else
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -1
    End

[spranay]

Not sure what version of SQL are you using. if it is 2005, you can make use of TRY CATCH block... this will reduce checking of errors on all actions.