Help of Avoiding Cursor. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help of Avoiding Cursor.

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-07-30 : 10:45:50

Folks,

I have a stored procedure which updates a table with the month end data. The proc is written using cursors and wanted to check whether we can avoid the cursor and rewrite the proc. Any help in rewriting the proc is appreciated. Below is the proc.

********************************************************************
CREATE PROC updatealloc
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS

DECLARE @HierarchyIDPropID INT
SET @HierarchyIDPropID = 517

DECLARE @PortGrpIDPropID INT
SET @PortGrpIDPropID = 556

DECLARE @UpdateDate DATETIME
SET @UpdateDate = GETDATE()

-- Declare temporary table
DECLARE @tblDetail TABLE
(
PortGrpID INT,
AsOf DATETIME,
HierarchyID INT,
MktValue_P FLOAT,
PctOfTotal_P FLOAT
)

DECLARE @PortGrpID INT

DECLARE CursorPortGrp CURSOR
FOR
SELECT T1.PropertyValue AS PortGrpID
FROM COSTING..tblPortDataInt T1
WHERE T1.PropertyID = @PortGrpIDPropID AND
T1.AsOfDate = (SELECT MAX(AsOfDate) FROM COSTING..tblPortDataInt T2
WHERE T2.PortID = T1.PortID AND
T2.PropertyID = T1.PropertyID)

-- Delete any existing records in the table for the asof date
DELETE Performance..tblHistAllocPortGrp
WHERE AsOf >= @StartDate AND AsOf <= @EndDate

DECLARE @NoMoreRecords BIT
SET @NoMoreRecords = 0

DECLARE @TotalMktValue_P FLOAT

DECLARE @MonthEnd DATETIME
SET @MonthEnd = Admin.dbo.fnGGenUseMonthEnd(@StartDate)

WHILE (@MonthEnd <= @EndDate)
BEGIN
OPEN CursorPortGrp
WHILE (@NoMoreRecords = 0)
BEGIN
-- Get the next portfolio group ID
FETCH NEXT FROM CursorPortGrp INTO @PortGrpID
IF (@@FETCH_STATUS <> 0)
BEGIN
SET @NoMoreRecords = 1
END
ELSE
BEGIN
-- Get the portfolio group holdings
INSERT INTO @tblDetail
(
PortGrpID,
AsOf,
HierarchyID,
MktValue_P,
PctOfTotal_P
)
SELECT @PortGrpID,
@MonthEnd,
T01.HierarchyID,
CASE
WHEN ABS((T01.MarketValue + T01.AI)) <= 1 THEN 0
ELSE (T01.MarketValue + T01.AI)
END AS MktValue_P,
0 AS PctOfTotal_P
FROM Admin.dbo.fnRCliRptGetHldDtl(@PortGrpID, @MonthEnd) AS T01

-- Calculate the total capital and percent of total for each sector group
SELECT @TotalMktValue_P = SUM(MktValue_P)
FROM @tblDetail
WHERE PortGrpID = @PortGrpID AND
AsOf = @MonthEnd

IF @TotalMktValue_P <> 0
BEGIN
UPDATE @tblDetail
SET PctOfTotal_P = MktValue_P / @TotalMktValue_P
WHERE PortGrpID = @PortGrpID AND
AsOf = @MonthEnd
END
END
END

CLOSE CursorPortGrp

-- Add the new records
INSERT INTO Performance..tblHistAllocPortGrp
(
AsOf,
PortGrpID,
HierarchyID,
PercentOfNetAssets,
UpdateDate
)
SELECT AsOf,
PortGrpID,
HierarchyID,
SUM(PctOfTotal_P) AS PercentOfNetAssets,
@UpdateDate AS UpdateDate
FROM @tblDetail
GROUP BY PortGrpID,
AsOf,
HierarchyID
ORDER BY PortGrpID

SET @MonthEnd = Admin.dbo.fnGGenUseMonthEnd(DATEADD(Month, 1, @MonthEnd))
DELETE FROM @tblDetail
SET @NoMoreRecords = 0
END

DEALLOCATE CursorPortGrp

*********************************************************************

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 11:44:35

The cursor itself will not help.
Please post sample data and expected result after update is made.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources