changes to 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 2005 Forums

Transact-SQL (2005)

changes to cursor

Author

Topic

supersql
Yak Posting Veteran

99 Posts

Posted - 2008-07-30 : 12:07:10

How could i replace this cursor for better performance or to a CTE

DECLARE cur_InspectionGroup CURSOR FAST_FORWARD FOR
SELECT DISTINCT
dbo.InspectionItems.ItemId,
dbo.InspectionItems.InspectionItemName,
dbo.InspectionItems.ZoneInstanceId,
dbo.InspectionItems.ScoreTypeId
FROM
dbo.Audits INNER JOIN
dbo.InspectionItems ON dbo.Audits.FloorId = dbo.InspectionItems.FloorId
WHERE
(dbo.Audits.AuditId = @AuditId) AND
(dbo.InspectionItems.Active = 1)

UNION
SELECT
dbo.InspectionItems.ItemId,
dbo.InspectionItems.InspectionItemName,
dbo.InspectionItems.ZoneInstanceId,
dbo.InspectionItems.ScoreTypeId
FROM
dbo.Audits_InspectionItems INNER JOIN
dbo.InspectionItems ON dbo.Audits_InspectionItems.InspectionItemId = dbo.InspectionItems.ItemId
WHERE
--(ISNULL(dbo.Audits_InspectionItems.SubInspectionItemId,0) = 0) AND
(dbo.Audits_InspectionItems.AuditId = @AuditId)
ORDER BY
dbo.InspectionItems.InspectionItemName

OPEN cur_InspectionGroup

DECLARE @GroupId int,
@GroupName varchar(100),
@ZoneInstanceId int,
@ScoreType int
FETCH cur_InspectionGroup INTO @GroupId, @GroupName, @ZoneInstanceId, @ScoreType
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @GroupScore float,
@InspItemId int,
@InspectedZoneId int,
@ItemComment varchar(200),
@PrevScore float

SET @GroupScore = null
SET @InspItemId = 0
SET @InspectedZoneId = 0
SET @ItemComment = ''
SET @PrevScore = null

SELECT TOP 1
@InspItemId = dbo.Audits_InspectionItems.ItemId,
@GroupScore = dbo.Audits_ItemsScore.[Value],
@InspectedZoneId = dbo.Audits_ItemsScore.ZoneInstanceId
FROM
dbo.Audits_InspectionItems INNER JOIN
dbo.Audits_ItemsScore ON dbo.Audits_InspectionItems.ItemId = dbo.Audits_ItemsScore.AuditInspectionItemId
WHERE
(dbo.Audits_InspectionItems.AuditId = @AuditId) AND
(dbo.Audits_InspectionItems.InspectionItemId = @GroupId) AND
(dbo.Audits_InspectionItems.SubInspectionItemId = 0)

SELECT @ItemComment = Comment FROM Audits_Comments WHERE AuditInspectionItemId = @InspItemId

IF(@InspectedZoneId = 0)
BEGIN
SET @InspectedZoneId = @ZoneInstanceId
END

--Get any previous scores for this record
SELECT @PrevScore = Score FROM #tmpPreviousAuditScores WHERE (InspectionItemId = @GroupId) AND (SubInspectionItemId = 0)

--Inject the header row
INSERT INTO #tmp (AuditItemID,AuditItemName,IsGroupHeader,GroupHeaderHasItems, ZoneID, Score, InspectionItemId, Comment, ScoreTypeId, PreviousScore)
VALUES(@GroupId, @GroupName,1,0,@InspectedZoneId, @GroupScore, @InspItemId, @ItemComment, @ScoreType, @PrevScore)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 12:55:50

It would be much easier if you can explain what you're trying to achieve here with some sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources