Stored Procedure Help

[faccorp]

Hi,

I currently have a stored procedure where I pass in the ID of a customer, and the stored procedure performs sum selects and updates a field in the same customer table. Like this;

CREATE PROCEDURE [dbo].[Balance]

(
	@cusid Integer
)

AS

UPDATE CUSTOMERS

SET BALANCE =

(SELECT SUM(Amount)
	FROM dbo.CUSTOMERS INNER JOIN
             dbo.Transactions ON dbo.CUSTOMERS.ID = dbo.Transactions.[Customer ID]
	WHERE (dbo.Transactions.[Customer ID] = @cusid))


WHERE ID = @cusid
GO

But what if I want the stored procedure to automatically fill the 'BALANCE' column for ALL the patients. I'm not sure how to efficiently 'loop' through all the customers and track the @cusid variable.

Thanks

 

[nigelrivett]

UPDATE CUSTOMERS
SET BALANCE =
(SELECT SUM(Amount)
FROM dbo.Transactions WHERE dbo.CUSTOMERS.ID = dbo.Transactions.[Customer ID])



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[faccorp]

Thanks,

But when I run this, each customer ends up getting the same balance written to the BALANCE column. And the Balance displayed is the total balance for ALL the customers. The BALANCE column should only show the balance for the specific customer.

Thanks Again

 

[nigelrivett]

Check your typing.
Note that CUSTOMERS does not appear as a table in the subquery.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dbomrrsm]

CREATE TABLE #TEMP (Amount money, CUSTID int)
INSERT INTO #TEMP
SELECT SUM(Amount),[Customer ID]
FROM dbo.Transactions 
GROUP BY [Customer ID]
GO
UPDATE CUSTOMERS
SET BALANCE = Amount
FROM CUSTOMERS as C INNER JOIN #TEMP as T
ON C.ID = T.CUSTID
GO

[blue]DBomrrsm[/blue]

 

[nigelrivett]

if you want to use a join rather than a correlated subquery then

UPDATE CUSTOMERS
SET BALANCE = Amount
FROM CUSTOMERS as C
INNER JOIN (SELECT Amount = SUM(Amount),CUSTID = [Customer ID]
FROM dbo.Transactions
GROUP BY [Customer ID])as T
ON C.ID = T.CUSTID


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.