Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically:


Is the following usage of @@ROWCOUNT in conjunction with XACT_ABORT OK to handle such a multi-operation transaction where if either one (in this case) fails, all should be rolled back. It looks OK, but I wonder if there are edge cases that should be accounted for.
Is there a handier way of checking existence than that construct? I suppose if there is a unique constraint in Table1Value, there shouldn't be a problem.


The query:

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT 
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL     
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
UPDATE Table2
SET
    Timestamp = GETUTCDATE(),
    VersionCounter = VersionCounter + 1
WHERE
    Table1Value = @table1Value AND @table1Value IS NOT NULL
    AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL
    AND @@ROWCOUNT &gt; 0;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure. 
SET @ROWCOUNT = @@ROWCOUNT; 
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;
To my surprise it looks like this works.  I wasn't aware that during the next query @@rowcount persisted from the previous query.  I don't know of any particular cases where it wouldn't but I would still probably change it to this just in case.

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT 
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL     
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
SET @ROWCOUNT = @@ROWCOUNT; 

IF @ROWCOUNT &gt; 0
    UPDATE Table2
    SET
        Timestamp = GETUTCDATE(),
        VersionCounter = VersionCounter + 1
    WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
        AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure. 

SET @ROWCOUNT = @@ROWCOUNT; 
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;