March 21, 2010 at 7:06 am
Hi there!
I went through google’s search result for sql server 2000 paging, which gave me an idea on how paging works but this does not solve the issue we currently have.
We do have a couple of stored procedures running and my boss now wants to add custom paging to it. I do not want to rewrite those procedures but instead I would like to this (please advise if this is possible):
1 – create a UDF or stored procedure that accepts the stored procedure name, page size, page
2 – execute the stored procedure and add a row number to it (may not be possible as the column names are unknown and varies)
3 – (OR) insert the result set into a temporary table or table variable (but again the column names are unknown and it varies)
4 – filter it according to the page size, page
I don’t know if this is possible and if it is how to go about this. I would appreciate any insight from you 🙂
Thanks and regards,
ruxan
March 22, 2010 at 8:00 am
If you want this new paging process to be even remotely efficient you WILL have to refactor it. In doing this many times for a variety of clients in the past I have found that universally the best mechanism is dynamic sql, for a variety of reasons. It isn't easy, but it usually beats all other methods and often by an order of magnitude or more in performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 22, 2010 at 3:50 pm
I don't think any universal thing will work here.
Pagination normally goes together with line sorting.
And to define which column to use in ORDER BY you need to change the procedure.
So, the way to go is to pass pagination parameters to procedures and to use SET ROWCOUNT inside of every procedure.
GROUPING ... WITH ROLLUP would be also useful for getting total number of rows to display on UI.
Of course, you may go "front end application way" - getting procedure metadata, create dynamical tables, and so on, but performance hit would be terrible.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply