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

[tesuji]

hi,

how do you access the database? If by ODBC, you should set autocommit OFF. Unfortunately, ODBC's default is ON!

krs,
tesu

[stockton]

I use the following PHP to do the call

/*
 *  Call spIssueScannedTickets stored procedure to update BundleIssued and TicketIssued
 *                      required parameters are:-
 *                              MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, UserID, Invalid and
 *                                      SlotsBalance & TableBalance & BonusBalance
 *                                      ie the amount of tickets to issue for Slots, Tables & Bonus
 */

        $Result = 0;
        $proc = mssql_init("spIssueScannedTickets",$link);
        mssql_bind($proc, "@MemberNum", $MemberNum, SQLVARCHAR);
        mssql_bind($proc, "@iEventID", $EventID, SQLINT2);
        mssql_bind($proc, "@BatchSize", $SizeOfBundle, SQLINT2);
        mssql_bind($proc, "@FirstNumber", $FirstNumber, SQLVARCHAR);
        mssql_bind($proc, "@LastNumber", $LastNumber, SQLVARCHAR);
        mssql_bind($proc, "@UserID", $_SESSION['UID'], SQLINT2);
        mssql_bind($proc, "@SlotsBalance", $SlotsToIssue, SQLINT2);
        mssql_bind($proc, "@TableBalance", $TableToIssue, SQLINT2);
        mssql_bind($proc, "@BonusBalance", $BonusToIssue, SQLINT2);
        mssql_bind($proc, "RETVAL", $Result, SQLINT2);
        mssql_execute($proc);
        mssql_free_statement($proc);

and to link to the database I use

<?php
function DBConnect() {
    global $dbservertype, $servername, $dbusername, $dbpassword, $dbname, $link;
//      echo "<br>In DBConnect";
    $link = mssql_connect($servername, $dbusername, $dbpassword);
    if (!$link) {
        echo "Error = " . mssql_get_last_message();
        sprintf($Message, "At %d in %s Could not connect to %s at %s as %s",__LINE__, __FILE__, $dbname, $servername, $dbusername);
        trigger_error(E_USER_ERROR, $Message);
        exit;
        }

    if (!(mssql_select_db($dbname, $link))) {
        echo "Error = " . mssql_get_last_message();
        sprintf($Message, "At %d in %s Could not select %s at %s as %s",__LINE__, __FILE__, $dbname, $servername, $dbusername);
        trigger_error(E_USER_ERROR, $Message);
        exit;
        }
    return $link;
    }
?>

[tesuji]

Hello,

php default's is auto commit ON. Therefore, you first step should always be switching it OFF, if you want to do serious database programming. With auto commit ON you will never be able to update two tables correctly, if they correlate, apart from being fond of always playing Russian roulette.

krs,
tesu