[SOLVED] Stored Procedure to reliably update two tables.

[stockton]

I have written the following Stored Procedure in an attempt to update two tables in the same database reliably but unfortunately it is not too successful. I ocassionaly end up with only the BundlesIssued tables updated and nothing in TicketsIssued.

Please make suggestions on how I could make this stored procedure update both tables reliably.

 

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)
if  (@SlotsBalance>0)

-- 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

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

-- 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

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