Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: How do I loop thru 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 uses not 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 PROCEDURE dbo.OPA_GetMenuItems
    AS
    Declare @i tinyint ,
    @tc tinyint
    Set @i = 1

    /* Select for top level menu items*/

    SELECT id, label, url, sort
    FROM mainNav
    ORDER BY sort

    Set @tc = @@rowcount

    while @i <= @tc

    begin
    Set @i = (@i + 1)

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

    RETURN

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I'm thinking that what you really want to do is perform a join and then handle the presentation stuff on the client side. I can't think of any valid reason why you would want to write your stored proc in the manner you have outlined above.
    Code:
    SELECT 
      m.id as MainID, 
      m.label as MainLabel, 
      m.url as MainUrl, 
      m.sort as MainSort, 
      s.id, 
      s.label, 
      s.url, 
      s.sort
    FROM 
      mainNav m inner join SubNav s
    ORDER BY 
      m.sort, 
      s.sort
    Regards,

    hmscott
    Last edited by hmscott; 01-17-06 at 11:59.
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, you definitely seem hazy on the SQL concept. What is the output format you want for your dropdown list? Give us a sample of the data you want to display.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2006
    Posts
    3
    The objective is to return a .Net dataset that contains a series of recordsets . The first recordset is the items contained in the top bar of the menu site. The subsequest recordsets contain the submenus for each item in the top menu.

    The challenge is that it's not a simple binding issue once the data is returned from the stored proc. Some items in the top bar may not have a submenu and therefore require different html and javascript.

    Inorder to render a lightweight, css-based (and W3C compliant) menu system, I need to determine at runtime which items have submenus and which don't

    - items with no submenu need to have code that only closes other menus
    - items with submenus need to have the closing code and also code to open there respective submenu.

    A working (static) example would be something like http://peelcas.org/home/index.aspx

    In order to pull this off, I have a c# routine that loops thru the dataset and renders the html.


    Here's the c# code

    ///////////////////////////
    // build global menu system
    ///////////////////////////

    string sqlConnstring = ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString;
    DataSet NavData = new DataSet();
    NavData = SqlHelper.ExecuteDataset(sqlConnstring, CommandType.StoredProcedure, "OPA_GetMenuItems");
    //Response.Write(NavData.Tables.Count.ToString());

    int i = 1; // counter for looping thru tables collection
    int tc = NavData.Tables.Count - 1;
    System.Text.StringBuilder sbNavLinks = new StringBuilder();
    System.Text.StringBuilder sbSubLinks = new StringBuilder();
    string url;
    //string webSectionName;
    string label;
    string anchorId;
    string menuId;

    // build main nav bar
    sbNavLinks.Append("<div id=\"navBar\"><ul>\r");
    while (i < tc)
    {
    label = NavData.Tables[0].Rows[i].ItemArray[1].ToString();
    anchorId = NavData.Tables[0].Rows[i].ItemArray[3].ToString();
    url = NavData.Tables[0].Rows[i].ItemArray[2].ToString();

    // no submenu items for this web section therefore...
    // ...create main nav bar link with global menu closing javascript only
    if (NavData.Tables[i].Rows.Count == 0)
    {
    sbNavLinks.Append("<li><a href=\""
    + url + "\" onmouseover=\"P7_autoLayers(0);\">"
    + label + "</a></li>\r");
    }
    // has submenu items therefore...
    // ...create main navbar link with submenu opening javascript
    else
    {
    // main navbar link
    sbNavLinks.Append("<li><a href=\""
    + url + "\""
    + " id=\"Anchor" + anchorId + "\""
    + " onmouseover=\"P7_autoLayers(0,'subMenu" + anchorId + "');"
    + "P7_Snap('Anchor" + anchorId + "','subMenu" + anchorId + "',0,24);"
    + "\">"
    + label + "</a></li>\r");
    }
    i++;
    }
    sbNavLinks.Append("</ul></div>\r");
    navBar.Text = sbNavLinks.ToString();

    // build submenus

    int j = 0; // counter for looping thru rows in current table
    int rc; // row count of current table
    i = 1;
    while (i < tc)
    {
    if (NavData.Tables[i].Rows.Count > 0)
    {
    // extract menu id for use in div id

    menuId = NavData.Tables[i].Rows[0].ItemArray[4].ToString();
    //Response.Write(webSectionSort);

    sbSubLinks.Append("<div id=\"subMenu" + menuId + "\" style=\"position:absolute; z-index:" + menuId + "; visibility: hidden;\">\r");
    sbSubLinks.Append(" <div class=\"subButton\">\r");
    sbSubLinks.Append(" <ul>\r");

    rc = NavData.Tables[i].Rows.Count -1;

    while (j <= rc)
    {
    // extract data for this link
    label = NavData.Tables[i].Rows[j].ItemArray[1].ToString();
    url = NavData.Tables[i].Rows[j].ItemArray[0].ToString();

    sbSubLinks.Append("<li><a href=\""
    + url + "\" class=\"subButton\">"
    + label + "</a></li>\r");
    j++;
    }

    sbSubLinks.Append("</ul></div></div>\r\r");
    }
    i++;
    j = 0;
    }
    subNavBar.Text = sbSubLinks.ToString();
    NavData.Dispose();



    Yes I too dislike all this looping business, but I don't have a way of binding and achieving the desired results...

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    What about a left join, instead of an inner join. With a left join, you could easily determine the MainMenu items with no submenus (the submenu fields would be null).

    You would still loop through it on the web server.

    Alternatively, you could use the FOR XML clause to create an XML string (which you would have to wrap inside of xml tags to make compliant).

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not a C coder. You need to give us the layout of the recordset that you want to get from the server. We can help you with that, and from there on its up to you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2006
    Posts
    3
    Thanks for the offer...

    I think hmscott's suggestion of a Left Join might just work, so I will groove on that for a while. As much as you're not a C# guy, I'm not a Sql guy, so I often just need a nudge in the right directlon..

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •