• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Stored Procedure, recordset paging?

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?
0
kevp75
Asked:
kevp75
  • 3
  • 2
3 Solutions
 
Chris LuttrellSenior Database ArchitectCommented:
>>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.
0
 
Anthony PerkinsCommented:
I could not agree, more.
0
 
kevp75Author Commented:
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?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
Chris LuttrellSenior Database ArchitectCommented:
Ah, here is an example:
declare @table_name varchar(128)
 
set @table_name = N'tbl_email_type'
 
select 
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

0
 
kevp75Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now