Re: Account Credit Stored Procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/17/04

  • Next message: Laphan: "Re: SQL Query Quandry" 
    Date: Sat, 17 Apr 2004 16:35:59 -0400

    You started off OK:

    CREATE PROCEDURE sp_GenAccCredit
    (
     @GenAcc_Date datetime,
     @GenAcc_Type tinyint,
     @GenAcc_MemID int,
     @GenAcc_UserID int,
     @GenAcc_Amount money
    )
    AS
     SET NOCOUNT ON

    BEGIN TRAN

    INSERT INTO GeneralAcc(GenAcc_Date, GenAcc_Type, GenAcc_MemID,
    GenAcc_UserID, GenAcc_Amount) VALUES (@GenAcc_Date, @GenAcc_Type,
    @GenAcc_MemID, @GenAcc_UserID, @GenAcc_Amount);

    IF @@ERROR > 0
    BEGIN
        RAISERROR ('Unable to insert into GeneralAcc.", 16, 1)
        ROLLBACK TRAN
        RETURN
    END

    /* You don't need this. In fact, it re-inserts the same data back into the
    table
     SELECT GenAcc_TransNo, GenAcc_Date, GenAcc_Type, GenAcc_MemID,
    GenAcc_UserID, GenAcc_Amount FROM GeneralAcc WHERE (GenAcc_TransNo =
    @@IDENTITY);
    */

    update User_AccBal
    set
        MyCol = MyCol + @Gen_Acc_Amount
    where
        GenAcc_MemID = @GenAcc_MemID

    IF @@ERROR > 0
    BEGIN
        RAISERROR ('Unable to update User_AccBal.", 16, 1)
        ROLLBACK TRAN
        RETURN
    END
    ELSE
        COMMIT TRAN

    GO 

    -- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Brian Candy" <brian@01792.com> wrote in message
news:%23E1wHmJJEHA.3104@TK2MSFTNGP10.phx.gbl...
I guess this is a bit cheeky asking someone to do my work for me, but I am
just unable to locate a book that teaches how to build stored procedures in
MSSQL. I have started to get the gist of things but am getting beyond my
knowledge faster than I can learn.
Does anyone know of a good example stored procedure that will insert a
record into say a bank account table, and at the same time update a
customers record to reflect the credit received. All within a stored
procedure which incorporates transactions to ensure the process completes
correctly.
Transaction starts
Record gets inserted into a table called GeneralAcc indicating the
transaction number, date & time of transaction, plus other details such as
transaction type and description etc..
Customers Record gets updated in the Members table. A field in their record
called User_AccBal needs to be read and have the amount of this transaction
calculated against it then the record needs to be updated.
Transaction Ends
If you can point me in the direction of an example of such a procedure, I
would be eternally grateful (well at least until tea time anyway).
Cheers
Brian Candy
CREATE PROCEDURE sp_GenAccCredit
(
 @GenAcc_Date datetime,
 @GenAcc_Type tinyint,
 @GenAcc_MemID int,
 @GenAcc_UserID int,
 @GenAcc_Amount money
)
AS
 SET NOCOUNT OFF;
INSERT INTO GeneralAcc(GenAcc_Date, GenAcc_Type, GenAcc_MemID,
GenAcc_UserID, GenAcc_Amount) VALUES (@GenAcc_Date, @GenAcc_Type,
@GenAcc_MemID, @GenAcc_UserID, @GenAcc_Amount);
 SELECT GenAcc_TransNo, GenAcc_Date, GenAcc_Type, GenAcc_MemID,
GenAcc_UserID, GenAcc_Amount FROM GeneralAcc WHERE (GenAcc_TransNo =
@@IDENTITY);
GO
  • Next message: Laphan: "Re: SQL Query Quandry"

    Relevant Pages