cursor and insert statement issue

[rds80]

insert @CIDS(Controlid, ControlidOrderKey)
select Controlid, ControlIDOrderKey from #CIDS order by controlidorderkey

SELECT ControlID FROM @CIDS

Shows
ControlID: 00-0385, 00-0375

DROP TABLE #CIDS

DECLARE ControlID_cursor CURSOR FOR
SELECT Controlid FROM @CIDS

OPEN ControlID_cursor
FETCH NEXT FROM ControlID_cursor INTO @ControlIdNS

SELECT @ControlIdNS AS 'ControlIdns'

Shows:
ControlID: 00-0385

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @NoteStack

SELECT @ControlIdNS,
N.NoteID,
FROM dbo.udfActiveReportNoteInformation(@ControlIdNS, NULL, NULL, 0) udfARNI
INNER JOIN tblNote N
ON N.NoteId = udfARNI.NoteID
ORDER BY udfARNI.NoteIDSortOrder

FETCH NEXT FROM ControlID_cursor
END

Shows:

ControlID NoteID
00-0385 21897
00-0385 21930
00-0385 21897
00-0385 21930

I'm not sure why I'm not getting values for ControlID: 00-0375 (the controlid does have corresponding NoteIDs) and why NoteID: 21897 and 21930 are being repeated twice.

My main question is do I have the insert and select statement properly in the cursor.

Thanks.

 

[bborissov]

You forgot to put where you want to fetch:

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @NoteStack

    SELECT @ControlIdNS,
        N.NoteID,
    FROM dbo.udfActiveReportNoteInformation(@ControlIdNS, NULL, NULL, 0) udfARNI
    INNER JOIN tblNote N
        ON N.NoteId = udfARNI.NoteID
    ORDER BY udfARNI.NoteIDSortOrder

    FETCH NEXT FROM ControlID_cursor [b][COLOR=red]INTO @ControlIdNS[/color][/b]
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rds80]

That worked perfectly.

Next issue is that I'm trying to insert into @Amount the ControlID, NoteID, and the Amount from @NoteStack. But none of the records from @NoteStack are being inserted into @Amount. I tried moving the @Amount table inside the cursor, but that didn't resolve the problem. Before I had the cursor @Amount was being populated properly.


SELECT ControlID FROM @CIDS

Shows
ControlID: 00-0385, 00-0375

DROP TABLE #CIDS

DECLARE ControlID_cursor CURSOR FOR
SELECT Controlid FROM @CIDS

OPEN ControlID_cursor
FETCH NEXT FROM ControlID_cursor INTO @ControlIdNS

Shows:
ControlID: 00-0385

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @NoteStack

SELECT @ControlIdNS,
N.NoteID,
N.Amount,
N.LPO,
N.Priority
FROM dbo.udfActiveReportNoteInformation(@ControlIdNS, NULL, NULL, 0) udfARNI
INNER JOIN tblNote N
ON N.NoteId = udfARNI.NoteID
ORDER BY udfARNI.NoteIDSortOrder

FETCH NEXT FROM ControlID_cursor INTO @ControlIDns
END

CLOSE ControlID_cursor
DEALLOCATE ControlID_cursor

SELECT * FROM @NoteStack

Shows:
ControlID NoteID LPO Priority Amount
00-0385 21897 1 1 4000.00
00-0385 21930 1 1 2000.00
00-0375 21933 1 1 1000.00
00-0375 21934 2 1 2000.00



INSERT INTO @Amount

SELECT UNs.ControlID,
UNS.NoteID,
(SELECT NS.Numerator
FROM @NoteStack NS
WHERE ((UNS.LPO > NS.LPO)
OR (UNS.LPO = NS.LPO AND UNS.Priority >= NS.Priority)))
FROM @NoteStack UNS

SELECT * FROM @Amount

So what I would like in the @Amount table is:

ControlID NoteID Amount
00-0385 21897 4000.00
00-0385 21930 2000.00
00-0375 21933 1000.00
00-0375 21934 3000.00

Thanks.

 

[rds80]

Do I need to provide more info to make the question more clear?