Stored Procedure, recordset paging?

Posted on 2009-04-27
Medium Priority
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
  • 3
  • 2
LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 24246184
>>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
ID: 24246848
I could not agree, more.
LVL 25

Author Comment

ID: 24250106
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?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 2000 total points
ID: 24250602
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, http://msdn.microsoft.com/en-us/library/aa260604.aspx.  All the old scripts for retrieving table metadata from 2000 involved sysobjects and syscolumns.  You can probably Google some queries also.
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 2000 total points
ID: 24250631
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 o.id = c.id 
where o.name = @table_name
order by c.colorder

Open in new window

LVL 25

Author Closing Comment

ID: 31575200
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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 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