How do I loop through a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause usesnot the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...