Ad:

MS SQL Discussion Thread
Unsolved
Views: 4113

Jul 24th, 2008

Stored Procedure to reliably update two tables.

Expand Post »

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.

MS SQL Syntax (Toggle Plain Text)
 
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
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

stockton

Reputation Points: 20

Solved Threads: 0

Light Poster

Offline

32 posts
since Jul 2008

Permalink

Jul 24th, 2008

Re: Stored Procedure to reliably update two tables.

hi,

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

krs,
tesu

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

Jul 25th, 2008

Re: Stored Procedure to reliably update two tables.

I use the following PHP to do the call

MS SQL Syntax (Toggle Plain Text)
/*
 *  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);
/*
 *  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

MS SQL Syntax (Toggle Plain Text)
<?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;
    }
?>
<?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;
    }
?>

Last edited by peter_budo; Jul 25th, 2008 at 9:18 am. Reason: Just correcting closing tag

stockton

Reputation Points: 20

Solved Threads: 0

Light Poster

Offline

32 posts
since Jul 2008

Permalink

Jul 25th, 2008

Re: Stored Procedure to reliably update two tables.

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Help with sql to select fields with equal values

Next Thread in MS SQL Forum Timeline: Help with Database Query...

DaniWeb Message

Cancel Changes

User Name
Password