Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: What is wrong with this code(paging SP)

    Code:
    CREATE PROCEDURE Page4
    (
        @startRowIndex int,
        @maximumRows int
    )
    AS
    
    DECLARE @first_id int, @startRow int
    	
    -- A check can be added to make sure @startRowIndex isn't > count(1)
    -- from employees before doing any actual work unless it is guaranteed
    -- the caller won't do that
    
    -- Get the first employeeID for our page of records
    SET ROWCOUNT @startRowIndex
    SELECT @first_id = CAST(DATE as int) FROM TOPIC
    
    -- Now, set the row count to MaximumRows and get
    -- all records >= @first_id
    SET ROWCOUNT @maximumRows
    
    SELECT * FROM TOPIC WHERE CAST(DATE as int) >= @first_id
    
    SET ROWCOUNT 0
    
    GO
    I have the date field clustered DESC

    The code always starts at the same place but maximum rows always adjusts to my input unlike the startrowindex. What am I doing wrong?

  2. #2
    Join Date
    Dec 2006
    Location
    UK
    Posts
    6
    ROWCOUNT only specifies the number of rows to return. So the below:

    SELECT @first_id = CAST(DATE as int) FROM TOPIC

    Is always starting from row 0, without a where cause or similar the result should always be the same initial value in the table. (I'm assuming in implied select before the cast)

    Did you mean to do something like:

    SET @first_id = (Select CAST(DATE as int) FROM TOPIC WHERE CAST(DATE as int)=@startRowIndex)

    Those casts are confusing but I'm assuming [Date] is your primary key and @startRowIndex is the first record to return (index value).

    Not 100% sure of this, but my best guess without playing with the code.

    J

    Edit: Just noticed what a pointless select statement I wrote, SET @first_id = @startRowIndex would have been the simpler equivalent (Doh).
    Last edited by jzelos; 12-09-06 at 09:45.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I usually point people here for paging:

    http://weblogs.sqlteam.com/jeffs/arc...3/22/1085.aspx

Posting Permissions

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