Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106

    Unanswered: sp nesting level exceeded

    I have a few tables with teams in a soccer-game and odds people have set up for these teams. What I want is to select the teams names and the three highest odds next to each team. The result should look something like this:

    Team Odds1 Odds2 Odds3
    ---------------------------------------------------
    Liverpool 3,22 3,01 2,98
    Draw 3,5 3,41 3,2
    Mancester Utd 1,98 1,75 1,72

    I have created a stored procedure to do this and I'm not sure if I have done it in the best way, but I get an error and I don't understand why. The error is "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)", but I can't understand why the loop is nesting. If anyone has a better way to do this I'm all ears. Here's the sp:

    Code:
    CREATE PROCEDURE dbo.sel_ListTopOdds AS
    DECLARE 
    @Cur cursor,
    @DescID int,
    @PlayerID int,
    @Odds1 float,
    @Odds2 float,
    @Odds3 float,
    @tmpOdds1 float,
    @tmpOdds2 float
    
    BEGIN
    
    DECLARE 
    @MyTable TABLE 
      (
      PlayerID int,
      Player varchar(200),
      Odds1 float,
      Odds2 float,
      Odds3 float
      )
    SET @DescID = 631    --for testing purposes
    
    INSERT INTO @MyTable (PlayerID, Player, Odds1)
    SELECT p.PlayerID, Player, 
      (SELECT MAX(Odds) FROM odds WHERE DescID = @DescID AND WinnerAlt = w.PlayerID)
    FROM games AS d, winner AS w, players AS p WITH (NOLOCK)
    WHERE d.DescID = @DescID
    AND w.DescID = d.DescID
    AND p.PlayerID = w.PlayerID
    
    SET @Cur = CURSOR FOR (SELECT PlayerID, Odds1 FROM @MyTable)
    OPEN @Cur
    FETCH NEXT FROM @Cur INTO @PlayerID, @Odds1
    
    WHILE (@@FETCH_STATUS = 0)
      BEGIN
        SET @tmpOdds1 = (SELECT MAX(Odds) FROM bob_ballot WHERE DescID = @DescID AND WinnerAlt = @PlayerID AND Odds < @Odds1)
        UPDATE @MyTable SET Odds2 = @tmpOdds1
        WHERE PlayerID = @PlayerID    
    
        SET @tmpOdds2 = (SELECT MAX(Odds) FROM odds WHERE DescID = @DescID AND WinnerAlt = @PlayerID AND Odds < @tmpOdds1)
        UPDATE @MyTable SET Odds3 = @tmpOdds2
        WHERE PlayerID = @PlayerID    
    
        FETCH NEXT FROM @Cur INTO @PlayerID, @Odds1
      END
    
    CLOSE @Cur
    DEALLOCATE @Cur
    END
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106

    Unhappy

    Stupid STUPID mistake!! For testing purposes I added "exec sel_ListTopOdds" at the end after the procedure but what I didn't realize was that it was treated as a part of the procedure, and offcourse that makes it recursive when it calls itself each time it finishes...I feel like a moron...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

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