SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Cycling through records in MSSQL2005

    I am making a stored procedure and am sending it a current ID and a mode (ie "next" or "prev")

    How do I create the SQL that would get the NEXT or PREVIOUS record from the CURRENTID, ensuring that if the current ID is at the end of the records, it would then select the first/last, ie:

    Record IDs: 1, 3, 6

    Current ID:1, next = 3
    Current ID:3, next = 6
    Current ID:6, next = 1
    Current ID:1, prev = 6

    Ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    SELECT TOP 1 columns FROM daTable WHERE id < $curr ORDER BY id DESC

    and since it's a stored proc, you don't have to have complicated logic built into the sql, you can instead check the database return code to see if the previous query returned anything

    for example, if WHERE id < $curr returns nothing, then you know you want the last row in the table, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty new to stored procedures. Are you saying that I need to conditionally just do the SQL and then check the records returned, and if they are 0, then run another SQL statement that gets the first/last record?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, I guess my next question is how do I do that? I created 3 (I guess they're called in-variables):

    @panelID varchar(16) - Determines which section of SQL to perform
    @currentID int - Determines the current ID
    @mode varchar(4) - Either "next" or "prev" to determine what next record to get

    Then I used IF/ELSE IF to get so far as:

    Code SQL:
    IF @panelID = 'games'
           IF @mode = 'next'
                 SELECT TOP 1 COLUMNS FROM games_table WHERE id_col > @currentID ORDER BY id_col ASC
      ELSE IF @mode = 'prev'
                 SELECT TOP 1 COLUMNS FROM games_table WHERE id_col < @currentID ORDER BY id_col ASC
    ELSE IF @panelID = 'reviews'
           IF @mode = 'next'
                 SELECT TOP 1 COLUMNS FROM reviews_table WHERE id_col > @currentID ORDER BY id_col ASC
           ELSE IF @mode = 'next'
                 SELECT TOP 1 COLUMNS FROM reviews_table WHERE id_col < @currentID ORDER BY id_col ASC

    I'm not sure how I can check the performed SQL's returned records and then subsequently run alternate code.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    use of TOP will not work correctly without the appropriate ORDER BY

    sorry, i don't know the sql server error codes

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, the code I have right now will get me everything until the recordset gets to the first/last record, then, understandably, there is no record < the first or > the last. I'm going to do some more research in seeing what MS SQL has available to cycle it over to the other side of the recordset.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    there's always this --
    Code:
    /* next */
    SELECT TOP 1
           id_col
         , list
         , of
         , columns
      FROM games_table 
     WHERE id_col IN ( ( SELECT MIN(id_col)
                           FROM games_table
                          WHERE id_col > @currentID )
                     , ( SELECT MIN(id_col)
                           FROM games_table )
    ORDER
        BY id_col DESC
    
    /* prev */
    SELECT TOP 1
           id_col
         , list
         , of
         , columns
      FROM games_table 
     WHERE id_col IN ( ( SELECT MAX(id_col)
                           FROM games_table
                          WHERE id_col < @currentID )
                     , ( SELECT MAX(id_col)
                           FROM games_table )
    ORDER
        BY id_col ASC
    slightly less efficient (two reads on every call) but the cycling is built in, as it were

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, I had to add a ')' before the ORDER BY in order to close off the 2 '(' located after the IN statement.

    I was spending a lot of time trying to get this working on my site when I thought (wait, I can just run a test exec in the enterprise manager!)

    So I ran your code on the Testimonials table (it has 2 records, ids: 1 and 3). I ran:

    exec nameofsp 'testimonial', 1, 'next'

    And it returned record 3, good so far. Then I ran

    exec nameofsp 'testimonial', 3, 'next'

    AND IT RETURNED record 1! WOOHOO! Just to be safe, I then ran:

    exec nameofsp 'testimonial', 1, 'prev'


    And up came record 3!

    I think my only possible problem comes when someone tries to navigate when only 1 record exists. Rather than involve more SQL, I'll just have the application language look at a record count variable that I put in tandem with your code, and if it's set to 1, then disable the navigation so that the stored proc is not called.

    Excellent code, Rudy!


Bookmarks

Posting Permissions

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