Stored Procedure, recordset paging?

Posted on 2009-04-27
Last Modified: 2012-06-27
Is it possible to create a stored procedure (sql 2000) to page through a recordset?

Without knowing the table name or columns?   So basically, a dynamic SP to page through a recordset based on the page#, records per page, table name, and columns?

How can I do this?
Question by:kevp75
    LVL 26

    Accepted Solution

    >>Is it possible to create a stored procedure (sql 2000) to page through a recordset?<<  Yes
    Totally Dynamic without knowing anything, Good Luck with that wish, it will be real ugly Dynamic SQL depending on what all you want to do with it and may not be efficent.  I have searched for and found examples before, mostly using TOP commands, but they often deteriate in performance as the dataset gets larger and you page through it.  It is better if you are doing it on a given query that you can work with know indexes and ordering to make it behave better.  You will have to keep up with current Page, page size, etc in your session and pass in each call.  Good luck.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    I could not agree, more.
    LVL 25

    Author Comment

    alright....   what about knowing the table names and columns?

    Wouldn't it be more efficient to make the database do the work for this, than in code?
    LVL 26

    Assisted Solution

    by:Chris Luttrell
    Sorry, I have been off of 2000 for too long to still have my old scripts or test one out for you.  Here is a link to a MS page on the system tables in 2000,  All the old scripts for retrieving table metadata from 2000 involved sysobjects and syscolumns.  You can probably Google some queries also.
    LVL 26

    Assisted Solution

    by:Chris Luttrell
    Ah, here is an example:
    declare @table_name varchar(128)
    set @table_name = N'tbl_email_type'
    o.[id] as 'table_id', 
    o.[name] as 'table_name',
    c.colorder as 'column_order',
    c.[name] as 'column_name'
    from sysobjects o inner join syscolumns c on = 
    where = @table_name
    order by c.colorder

    Open in new window

    LVL 25

    Author Closing Comment

    thanks folks....sorry it toook so long in gettting back to this.  I ended up doing it non-dynamically, and simply creating a paging procedure for each time I needed it

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now