Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 12th, 2007, 03:18 PM
Friend of Wrox
Join Date: May 2006
Posts: 107
Thanks: 1
Thanked 8 Times in 7 Posts
Default Stored procedure - batch update

I have a batch input system consisting of two tables which I've simplified below.

(Batch Table)
BatchID Int
BatchTotal Decimal(18.2)

(BatchTran Table)
BatchTranID Int
BatchTranHeaderID Int This links to BatchID
BatchTranValue Decimal(18.2)
BatchTranAccountNo Int

When the batch is complete I want to move the details from the Batch files and transfer them to the relevant Accounts files.

(Account Table)
AccountNoID Int
AccountTotal Decimal(18.2)

(AccountTran Table)
AccountTranBatchID Int
AccountTranBatchRef Int
AccountTranAmount Decimal(18.2)

I want to be able to run a stored procedure which selects all Batches and Transactions with a reference passed to the stored procedure (@BatchID) and create the entries in the AccountTran Table

    INSERT INTO AccountTran(AccountTranBatchID, AccountTranBatchRef, AccountTranAmount)
    SELECT BatchTranId, BatchTranHeaderID, BatchTranValue
    FROM BatchTran
    WHERE BatchTranHeaderID=@BatchID

No problems so far, the details are created perfectly.

What I want to do next is to take the values in BatchTranValue (which have been passed into AccountTranAmount) and add it to AccountTotal field

My question is Can this be completed in one stored procedure if so can you guide me as to how I achieve this?

Many thanks

Try our latest project www.nobanx.com Currency Exchange for members
Old December 12th, 2007, 03:24 PM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

Depends on what you mean.

If you are inserting a new row into Account Table you could simply do

INSERT INTO [Account Table](AccountTotal)
SELECT Sum(BatchTranValue) FROM BatchTran WHERE BatchTranHeaderID=@BatchID

Otherwise you could do something like:
DECLARE @Total decimal
SET @total = (SELECT AccountTotal from [Account Table] where AccountNoID = <value>

INSERT INTO [Account Table](AccountTotal)
SELECT Sum(BatchTranValue + @total) FROM BatchTran WHERE BatchTranHeaderID=@BatchID


================================================== =========
Read this if you want to know how to get a correct reply for your question:
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========
Old December 12th, 2007, 04:13 PM
Friend of Wrox
Join Date: May 2006
Posts: 107
Thanks: 1
Thanked 8 Times in 7 Posts

The record in Account Table will already exist.

I want to add the value of the field AccountTran.AccountTranAmount to the value already held in Account.AccountTotal

I will then end up with a new record in AccountTran and an updated total in Account (Hopefully)

I've tried the following

        AccountTotal = AccountTotal + BatchTranValue
        BatchTran AS BatchUpd
        Account AS AccountUpd
        AccountUpd.AccountRef = BatchUpd.BatchTranBatchID

But it is only processing the first record in BatchTran

I just can't see what it is I'm missing

Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing an UPDATE Stored Procedure ashg657 Access VBA 1 June 9th, 2006 06:29 AM
ADP Update Stored Procedure ashg657 Access 0 June 2nd, 2006 05:29 AM
Update Tables in Stored Procedure Talsiter SQL Server 2005 1 April 10th, 2006 03:32 PM
How to update diffgram through stored procedure rajjr SQL Server ASP 0 March 3rd, 2006 02:17 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.