Paging stored proc returning incorrect recs

Hi,

I've been looking for a way to page results from a very large table (1,000,000 or so recs) for a web app and came across this http://rosca.net/writing/articles/serverside_paging.asp, which discusses the use of a stored proc to page the results on the database server before passing them to the web page.  Sounds like it would do the job I want it to do perfectly and it works when I want the first page of results but as soon as I move on to page 2 of the results I end up with every row returned to me.  It's as if the 'where' clause and paging is being ignored :(

I'm a sql server noob and have never played with sp's before so could use some input from you lot!

This is a sample of the query as it's being run from the web page which produces 25 records all of which match my criteria:

SELECT_WITH_PAGING 'RegYear, RegPart1, RegPart2, Price', 'RegYear', 'PrefixStyle', '1', '25', '1', 'RegPart1 like ''%'' AND RegPart2 like ''DOG''', 'RegYear, RegPart1, RegPart2'

This is a sample of the query as it's being run from the web page which produces 700,000 rows:

SELECT_WITH_PAGING 'RegYear, RegPart1, RegPart2, Price', 'RegYear', 'PrefixStyle', '2', '25', '1', 'RegPart1 like ''%'' AND RegPart2 like ''DOG''', 'RegYear, RegPart1, RegPart2'

As you can see the only difference is that I asked for page 2!

This is the stored procedure in question:

CREATE PROCEDURE SELECT_WITH_PAGING (
      @strFields VARCHAR(4000) ,
      @strPK VARCHAR(100),
      @strTables VARCHAR(4000),
      @intPageNo INT = 1,
      @intPageSize INT = NULL,
      @blnGetRecordCount BIT = 0,
      @strFilter VARCHAR(8000) = NULL,
      @strSort VARCHAR(8000) = NULL,
      @strGroup VARCHAR(8000) = NULL
)

/*
      PURPOSE: executes a select statement as defined by the parameters and returns a particular page of data (or all rows) efficiently
      AUTHOR: Andrew Rosca (andrew@rosca.net)
      (c)2002 Andrew Rosca. You may use and distribute this code freely under the GNU licensing standard.
*/

 AS
      DECLARE @blnBringAllRecords BIT
      DECLARE @strPageNo VARCHAR(50)
      DECLARE @strPageSize VARCHAR(50)
      DECLARE @strSkippedRows VARCHAR(50)

      DECLARE @strFilterCriteria VARCHAR(8000)
      DECLARE @strSimpleFilter VARCHAR(8000)
      DECLARE @strSortCriteria VARCHAR(8000)
      DECLARE @strGroupCriteria VARCHAR(8000)

      DECLARE @intRecordcount INT
      DECLARE @intPagecount INT      


--******** NORMALIZE THE PAGING CRITERIA
      --if no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will set a flag that will help with that (blnBringAllRecords)

      IF @intPageNo < 1
            SET @intPageNo = 1

      SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
      
      IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO PAGING
            SET @blnBringAllRecords = 1
      ELSE
            BEGIN
                  SET @blnBringAllRecords = 0
                  SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
                  SET @strPageNo =  CONVERT(VARCHAR(50), @intPageNo)
                  SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo - 1))
            END





--******** NORMALIZE THE FILTER AND SORTING CRITERIA
      --if they are empty, we will avoid filtering and sorting, respectively, executing more efficient queries

      IF @strFilter IS NOT NULL AND @strFilter != ''
            BEGIN
                  SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
                  SET @strSimpleFilter = ' AND ' + @strFilter + ' '
            END
      ELSE
            BEGIN
                  SET @strSimpleFilter = ''
                  SET @strFilterCriteria = ''
            END

      IF @strSort IS NOT NULL AND @strSort != ''
            SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
      ELSE
            SET @strSortCriteria = ''

      IF @strGroup IS NOT NULL AND @strGroup != ''
            SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
      ELSE
            SET @strGroupCriteria = ''



--************************** NOW START DOING THE REAL WORK
--!NOTE: for potentially improved performance, use sp_executesql instead of EXEC

IF @blnBringAllRecords = 1 --ignore paging and run a simple select
BEGIN

      EXEC (
            'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
      )

END-- WE HAD TO BRING ALL RECORDS
ELSE            --BRING ONLY A PARTICULAR PAGE
BEGIN
      IF @intPageNo = 1 --in this case we can execute a more efficient query, with no subqueries
            EXEC (
                  'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
            )
      ELSE --execute a structure of subqueries that brings the correct page
            EXEC (
                  'SELECT "Running",' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
                        (SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
                              ' WHERE ' + @strPK + ' NOT IN ' + '
                                    (SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
                              @strSimpleFilter +
                              @strGroupCriteria +
                              @strSortCriteria + ') ' +
                        @strGroupCriteria +
                        @strSortCriteria
            )

END --WE HAD TO BRING A PARTICULAR PAGE


--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCount = 1
      IF @strGroupCriteria != ''
            EXEC (
                  'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
            )
      ELSE
            EXEC (
                  'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria
            )
GO
GPneedsHelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DanRollinsCommented:
I don't understand everything that the author was doing, but when the
    @intPageNo
parameter is other than 1, a different section of code is executed.  The error it casued was :


I got it to work by changing these lines:
     ELSE --execute a structure of subqueries that brings the correct page
          EXEC (
               'SELECT "Running",' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
 to:
      ELSE --execute a structure of subqueries that brings the correct page
          EXEC (
               'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
 
That is, I got rid of the word "Running" -- I don't know why it was there -- and it looks like it is working fine.

-- Dan
DanRollinsCommented:
(oops I forgot to finish that one thought)

The error it caused was:

      Server: Msg 207, Level 16, State 3, Line 1
      Invalid column name 'Running'.
DanRollinsCommented:
I also noticed that the SQL code fom the original article
       http://rosca.net/writing/articles/SelectWithPaging.sql.txt

does not use 'Running" anywhere... so I guess you accidentally added it or something.

-- Dan
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

GPneedsHelpAuthor Commented:
Woops... Yes I added that just so I could see which piece of the code was being run.  Theres a section that returns all rows if you specify pagesize of null or <1 and I imagined maybe that was being run instead, hence the addition.  Didn't cause an error at my end though, which is odd.

Regardless it doesn't work for me with or without my addition.  Anything other than page 1 (which returns the 25 rows I want) returns about 2/3 of the table contents!  You say it works at your end?
DanRollinsCommented:
All I could do is test against my own database with a relatively simple query -- and it worked as advertised.

One thiing to try is to simplify your query.  There is a chance that the
     'RegPart1 like ''%'' AND RegPart2 like ''DOG'''
is doing something odd.   Try it without a sort string or a select string (skip the last two parms in your query):

    SELECT_WITH_PAGING 'RegYear, RegPart1, RegPart2, Price', 'RegYear', 'PrefixStyle', 2, 25, 1

Just a note: Those three parms at the end of that are all numeric (declared as INT), so there is no need to wrap them in quotes when executing the SP.

-- Dan
GPneedsHelpAuthor Commented:
I tried your suggestion (cut and pasted from this page into sql query analyser) and it returned 152402 rows lol...
GPneedsHelpAuthor Commented:
Your help is much appreciated Dan but I guess it's beginning to look like an issue with my data rather than the sp isn't it?

The table that is being queried is a view if that makes a difference, but I doubt it would, would it?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will try the proc tomorrow morning on my side, but just 1 idea:
Do you have SET QUOTED_IDENTIFIER OFF or ON ?
DanRollinsCommented:
I suppose a view should work the same as a table.

I have no way to verify the correctness of the spelling of your column names and so forth.

In these kinds of problems, the first step is to get to a place where the SP works and then try adding back parameters etc. until it starts to fail.  So try a simpler query directly against a simple table... then try a simple query against the view... etc.

-- Dan
GPneedsHelpAuthor Commented:
I'm afraid I have no idea what 'QUOTED_IDENTIFIER' is!  Is this a setting on the view?  How can I check?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
QUOTED_IDENTIFIER is a setting of your session, which is set by default by the server settings.
You might try to enforce the setting to OFF when (re)createing and running the stored procedure.

Actually, this setting makes that you can use quotes to identify object names in your SQL:
select * from yourtable
select * from [yourtable]
select * from "yourtable"
are all equivalent if you have QUOTED_IDENTIFIER ON, however if it is turned off, the last one should fail

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanRollinsCommented:
Another diagnostic technique is to visually verify the dynamic SQL statment.  For instance, replace the large EXEC statement with:

      DECLARE @strSQL VARCHAR(8000)
      SET @strSQL=  
      'SELECT ' + @strFields + ' FROM ' + @strTables
          ' WHERE ' + @strPK + ' IN '
          + ' (SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables
          + ' WHERE ' + @strPK + ' NOT IN '
          + ' (SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM '
          + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') '
          + @strSimpleFilter
          + @strGroupCriteria
          + @strSortCriteria + ') '
          + @strGroupCriteria
          + @strSortCriteria

          PRINT @strSQL
          EXEC (@strSQL);

Thn after running the SP in Query Analyzer, check the "Messages" tab.  Sometimes just looking at the command shows what's wrong.  But you can copy that text and run it in another window and that may also yield some clues.

-- Dan
DanRollinsCommented:
I believe that useful suggestions were provided... for help diagnosing even if the specific problem could not be solved (most cannot be solved without the Asker's database, anyway :-).  
I recommend a split to Danrollins and AngelIII
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.