Stored Procedure to reliably update two tables.

ALTER PROCEDURE spIssueScannedTickets
        @iEventID int,
        @MemberNum nvarchar(12),
        @BatchSize int,
        @FirstNumber nvarchar(12),
        @LastNumber nvarchar(12),
        @SlotsBalance int,
        @TableBalance int,
        @BonusBalance int,
        @UserID int
AS
DECLARE @Result int

-- Declare variables used in error checking.
DECLARE @error_var int, @rowcount_var int


SET @Result = 0
BEGIN TRANSACTION
INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
        VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)

-- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-- check if an error occured and if the expected number of records were affected
IF @error_var <> 0 or @rowcount_var <> 1
BEGIN
   ROLLBACK
--   PRINT "Warning: Error on Insert 1"
   RETURN(-1)
END

if  (@SlotsBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
         VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)

-- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-- check if an error occured and if the expected number of records were affected
IF @error_var <> 0 or @rowcount_var <> 1
BEGIN
   ROLLBACK
--   PRINT "Warning: Error on Insert 2"
   RETURN(-2)
END

if  (@TableBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
         VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
if  (@BonusBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
         VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)

-- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-- check if an error occured and if the expected number of records were affected
IF @error_var <> 0 or @rowcount_var <> 1
BEGIN
   ROLLBACK
--   PRINT "Warning: Error on Insert 3"
   RETURN(-3)
END


COMMIT TRANSACTION
SET @Result = @BatchSize
RETURN @Result
GO

-- The function will return your batchSize (that was passed to it, or a Negative number if an error occured