March 1, 2002 at 10:07 am
can anybody help me to get rid of the cursor in the stored proc below?
CREATE PROC Update_Posting (@batchId varchar(50))
AS
DECLARE @caseNumber varchar(10)
DECLARE @receiptAmount varchar(20)
DECLARE update_cursor CURSOR FOR
SELECT CaseNumber, ReceiptAmount FROM currentpostings
WHERE batchId = @batchId
OPEN update_cursor
FETCH NEXT FROM update_cursor
INTO @caseNumber, @receiptAmount
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE CaseData SET TotalPaidIn = TotalPaidIn + @receiptAmount, BalanceOnHand = BalanceOnHand + @receiptAmount WHERE CaseNumber = @caseNumber
FETCH NEXT FROM update_cursor
INTO @caseNumber, @receiptAmount
END
CLOSE update_cursor
DEALLOCATE update_cursor
GO
March 1, 2002 at 10:22 am
sorry for the typo in the topic field!
March 1, 2002 at 11:14 am
update cd
SET TotalPaidIn = TotalPaidIn + cp.receiptamount
, BalanceOnHand = BalanceOnHand + cp.receiptamount
from casedata cd
inner join currentpostings cp
on cd.casenumber = cp.casenumber
WHERE cp.batchid = @batchid
Steve Jones
March 1, 2002 at 11:18 am
There two queries one using while function and the other without,both these queries donot use a cursor.
--Option1
CREATE PROC Update_Posting (@batchId varchar(50))
AS
WHILE EXISTS(SELECT CaseNumber, ReceiptAmount FROM currentpostings
WHERE batchId = @batchId)
BEGIN
UPDATE CaseData SET TotalPaidIn = A.TotalPaidIn + B.receiptAmount,
BalanceOnHand = A.BalanceOnHand + B.receiptAmount FROM CaseData A INNER JOIN currentpostings B
ON A.CaseNumber = B.CaseNumber
END
--Option2
CREATE PROC Update_Posting (@batchId varchar(50))
AS
UPDATE CaseData SET TotalPaidIn = A.TotalPaidIn + B.receiptAmount,
BalanceOnHand = A.BalanceOnHand + B.receiptAmount FROM CaseData A INNER JOIN currentpostings B
ON A.CaseNumber = B.CaseNumber AND B.BatchId = @BatchId
March 1, 2002 at 4:27 pm
Try this
UPDATE cd
SET TotalPaidIn = TotalPaidIn + cp.TotRec, BalanceOnHand = BalanceOnHand + cp.TotRec
FROM casedata cd
INNER JOIN
(SELECT CaseNumber, SUM(ReceiptAmount) AS TotRec
FROM currentpostings
WHERE batchId = @batchId
GROUP BY CaseNumber) AS cp
ON cd.CaseNumber = cp.CaseNumber
This should handle any CaseNumber where multiple postings occurr, they will only show one update but it will contain the full amount of all postings added in.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 4, 2002 at 8:05 am
thanks, Antares686.
it worked!
xmash999
quote:
Try thisUPDATE cd
SET TotalPaidIn = TotalPaidIn + cp.TotRec, BalanceOnHand = BalanceOnHand + cp.TotRec
FROM casedata cd
INNER JOIN
(SELECT CaseNumber, SUM(ReceiptAmount) AS TotRec
FROM currentpostings
WHERE batchId = @batchId
GROUP BY CaseNumber) AS cp
ON cd.CaseNumber = cp.CaseNumber
This should handle any CaseNumber where multiple postings occurr, they will only show one update but it will contain the full amount of all postings added in.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply