CurrentPage, TotalPages, TotalRows undocumented system variables

  • We found a posting on how to solve paging of large recordset, but after reviewing the code we noticed three variables that we can not find the official meaning of, (CurrentPage, TotalPages, TotalRows).  Can anyone reference a site or tell us want the variables do?  Code below

     

    CREATE PROCEDURE SampleCDs_Paging_Rowcount 

        @pagenum INT = 1, 

        @perpage INT = 50 

    AS 

    BEGIN 

        SET NOCOUNT ON 

     

        DECLARE 

            @ubound INT, 

            @lbound INT, 

            @pages INT, 

            @rows INT 

     

        SELECT 

            @rows = COUNT(*), 

            @pages = COUNT(*) / @perpage 

        FROM 

            SampleCDs WITH (NOLOCK) 

     

        IF @rows % @perpage != 0 SET @pages = @pages + 1 

        IF @pagenum < 1 SET @pagenum = 1 

        IF @pagenum > @pages SET @pagenum = @pages 

     

        SET @ubound = @perpage * @pagenum  

        SET @lbound = @ubound - (@perpage - 1)  

     

        SELECT 

            CurrentPage = @pagenum, 

            TotalPages = @pages, 

            TotalRows = @rows 

     

        -- this method determines the string values 

        -- for the first desired row, then sets the 

        -- rowcount to get it, plus the next n rows 

     

        DECLARE @aname VARCHAR(64), @title VARCHAR(64) 

     

        SET ROWCOUNT @lbound 

     

        SELECT 

            @aname = ArtistName, 

            @title = Title 

        FROM 

            SampleCDs WITH (NOLOCK) 

        ORDER BY 

            ArtistName, 

            Title 

     

        SET ROWCOUNT @perPage 

     

        SELECT 

            ArtistName, 

            Title 

        FROM 

            SampleCDs WITH (NOLOCK) 

        WHERE 

            ArtistName + '~' + Title 

            >= @aname + '~' + @title 

        ORDER BY 

            ArtistName, 

            Title 

     

        SET ROWCOUNT 0 

    END 

    GO

     

     

     

  • They're not variables. It looks like the procedure returns two resultsets. The first resultset is one row and three columns with the CurrentPage, TotalPages, TotalRows. You need that info to determine how many times to exec the procedure. The second resultset is the actual page of data.

    This method of paging looks pretty inefficient. I hope the table SampleCDs is pretty small.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply