Alternative to cursor

Hello,
I would like to find and alternative to using a cursor and updating my
table with the calculated values. The following code snippet is from my
store procedure.
-- Calculate and Update LEWeightedTerm and LEWeightedRR
DECLARE LECursor CURSOR FOR
SELECT TransactionID, Term, BusinessUnitID, LEAlloc, CalcODRBefore
FROM stage_DealTransaction
WHERE COBDateID = @.COBDateID AND LEWeightedTerm is null AND
LEWeightedRR is null
OPEN LECursor
DECLARE @.tranID AS int
DECLARE @.term AS int
DECLARE @.LEAlloc AS float
DECLARE @.BUID AS varchar(15)
DECLARE @.ODR AS Varchar(2)
DECLARE @.LEWT AS float
DECLARE @.LEWRR AS float
DECLARE @.LEAllocSum AS float
DECLARE @.rate AS float
FETCH NEXT FROM LECursor
INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
While (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.LEAllocSum = 1
IF (@.BUID IS NOT NULL)
BEGIN
SELECT @.LEAllocSum = SUM(ISNULL(LEAlloc, 0)) FROM DealTransaction
WHERE BusinessUnitID = @.BUID AND COBDateID = @.COBDateID
END
IF (@.LEAllocSum = 0)
BEGIN
SET @.LEAllocSum = 1
END
IF (@.LEAlloc IS NULL)
BEGIN
SET @.LEAlloc = 0
END
SET @.LEWT = (@.LEAlloc * ISNULL(@.term, 0)) / @.LEAllocSum
SET @.rate = CONVERT(float, ISNULL(@.ODR, '41'))
SET @.LEWRR = (@.LEAlloc * @.rate) / @.LEAllocSum
UPDATE stage_DealTransaction SET LEWeightedTerm = @.LEWT ,
LEWeightedRR = @.LEWRR WHERE TransactionID = @.tranID AND COBDateID =
@.COBDateID
END
FETCH NEXT FROM LECursor INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
END
CLOSE LECursor
DEALLOCATE LECursor
I would like to find a way to optimize it so that the updates can be
done in bulk rather than one record at a time.
Any feedback would be highly appreciated.
Thank you,
Zubinezubine@.gmail.com wrote:
> Hello,
> I would like to find and alternative to using a cursor and updating my
> table with the calculated values. The following code snippet is from
> my store procedure.
>
Could you summarize what you need this to do rather than making us analyze
the cursor code?
It always help to provide DDL and sample data as well. See
www.aspfaq.com/5006 for suggestions
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 18 Nov 2005 07:04:41 -0800, zubine@.gmail.com wrote:

>Hello,
>I would like to find and alternative to using a cursor and updating my
>table with the calculated values. The following code snippet is from my
>store procedure.
Hi Zubine,
Try the following:
UPDATE s
SET LEWeightedTerm = COALESCE (s.LEAlloc * s.Term), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1),
LEWeightedRR = COALESCE (s.LEAlloc *
CAST(COALESCE(s.CalcODRBefore, '41') AS float)), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1)
FROM stage_DealTransaction AS s
LEFT JOIN (SELECT BusinessUnitID,
SUM(LEAlloc) AS SumLEAlloc
FROM DealTransaction
WHERE dt.COBDateID = @.COBDateID
GROUP BY BusinessUnitID) AS dt
ON dt.BusinessUnitID = s.BusinessUnitID
WHERE s.COBDateID = @.COBDateID
AND s.LEWeightedTerm IS NULL
AND s.LEWeightedRR IS NULL
Depending on how your tables and data look, you might be able to replace
the LEFT JOIN with an INNER JOIN, and that might speed up the execution
as well.
Note that the stattement above is untested. See www.aspfaq.com/5006 if
you prefer a tested reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)