Sql server 2000 paging

[Ilyas]

Hi all

I need to implmenet paging across different tables. The tables all
have a different name eg Data01, data02 data03 etc, however they are
columns which are common to each table, but each table also has some
unique columns

My questions is that I want to display data from any one of these
tables - I wont know which one until runtime, but since they contains
large amounts of data, I only want to display say 10 at a time. Also
the database is on sql server 2000 so I cant use RowNumber

I am thinking of using dynamic sql, so I would have a stored procedure
which takes the following:
selectClause - the fields to pull out
tablename - name of the table to get data from
whereClause - any filter criteria to apply
sortColumn - the column to sort on
sortDirection - the direction to sort on

Inside this stored procedure I would have ot build up some sql and
then execute it. I cant think of any other way to do it. Can anyone
suggest any pointers or alternative options?

Many thanks

 

[S.M. Altaf [MVP]]

Have you considered normalizing your tables so that the common columns are
in one table with some identifier that allows you to join it with the now
unique tables data01, data02 and so on?

That aside, in your stored procedure, you can set a ROWCOUNT for your
dynamic SQL query. This means that you will need to calculate, either in
the SQL or in your calling code, what row numbers you need.

SET ROWCONT - http://msdn.microsoft.com/en-us/library/ms188774.aspx

 

[Fernando Rodriguez]

If you want to do true paging on the database end then you will aso need
pageSize and pageNumber parameters, and yes, the only way to do it that I
know of is using dynamic SQL. I usually build a command similar to the
following for this task:

declare @sql nvarchar(max);
declare @startRow int;
set @startRow = @pageSize * @pageNumber;

set @sql = '
select top ' + convert(nvarchar, @pageSize) + ' *
from mytable
where primaryKeyField not in
(select top ' + convert(nvarchar, @startRow) + ' primarykeyField
from mytable)';

exec(@sql);

If you don't know the all the field names at the time of writing the stored
proc then dynamic SQL is also the only way.

If you just want to know how many rows there is on the stored proc so that
you can do the paging calculations on the web server you might as well
download the whole resultset into a DataSet, then bind a grid with it and
let the grid take care of the paging. Of course doing it in the server is
much more efficient for large resultsets.

Hope that helps,
Fernando L Rodriguez, MCP