Business Intelligence

Dynamic Table Paging*

*If necessary

Paging data in your application, be it thin or thick client, is a frequent need: list of customers, address directory, invoice details, etc. – any number of situations may crop up where the paging grid in the UI becomes a necessity.

If your data is big enough to be paged in your display, then, for the most part, it is a large enough set that you are not advised to pull all of the set back at once to be cached by the UI, particularly if your storage medium is SQL Server. A lot of you are probably familiar with using row_number, the OVER statement and maybe even a CTE to resolve ordering the data and grabbing groups at a time for the application. But what do you do if you need to support paging, and you are not sure what the table is going to be?

I will point out that the title is asterisked, my caveat to this whole discussion is: should this be necessary. Beside that point though, it is a relatively interesting issue. How would you implement a paging query when you don’t know what table you might want, or when the schema might be unknown to you? (Again, this should not be used widespread, but scenarios can come up where this makes sense).

The first requirement in this scenario is getting table meta-data back to your application; in SQL 2005 and above, this is a simple matter of querying sys.tables. After you have the table list, the other two variables to declare in this scenario are your page size and what page in the paged data you desire. The full script is provided below – most of it is pretty straight forward, except that you will notice that I pull a little stunt in getting the column(s) to do the order by on for the row_number and over functions.

Given that we need something to order this data by, and we don’t know the schema, I query the information_schema tables to get the tables primary key column or columns – if none, it will just take the first column. Not the most advanced, but then again, this script shoots blind – which is the point of this example.

The other little bonus is that it also removes column types from the query that would likely choke an auto-column generating .Net control – varbinary, xml, etc. are excluded in the columns dynamically selected from the table parameter – to get a closer look at this, you can modify the script and print the @TableSelectColumns variable.

I would be interested to hear how others might apply this scenario, so if you are up for it, post how you used this or a similar script!

 

declare        

@TableObjectId int,            @PageSize int,

    @PageIndex int,            @TableRows int,

    @TableName nvarchar(75),        @KeyOrderColumn nvarchar(50),

    @StrSQL nvarchar(4000),        @PageRowStart int,

    @PageRowEnd int,            @TableSelectColumns nvarchar(2000),

    @Params nvarchar(50)

            

— Set the parameters

set @TableName = Your Table Name Here

set @PageSize = Your Page Size Here

set @PageIndex = Your Page Index here

 

set @StrSQL =     ‘select @columnsOUT = coalesce(@columnsOUT + ”,”, ””)+ ”[”+c.name+”]”

            from sys.columns c

            inner join sys.types t on t.system_type_id = c.system_type_id

            where c.object_id = OBJECT_ID(”’ + @TableName + ”’)

            and t.system_type_id not in (34,35,98,99, 165,173,241,231)’

            

set @Params = ‘@columnsOUT nvarchar(2000) output’

exec sp_executesql @StrSQL,@Params, @columnsOUT = @TableSelectColumns output

 

 

if(@PageIndex < 2)

    set @PageRowStart = 0

if(@PageIndex > 1)

    set @PageRowStart = ((@PageIndex * @PageSize) @PageSize)

    

set @PageRowEnd = (@PageIndex * @PageSize)

 

set @TableObjectId = (select OBJECT_ID(@TableName))

 

— select the key column(s)

select @KeyOrderColumn = coalesce(@KeyOrderColumn + ‘, ‘, )

+ ‘[‘+convert(nvarchar(25),B.COLUMN_NAME,20)+‘]’

from     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A

, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

where     CONSTRAINT_TYPE = ‘PRIMARY KEY’

and     A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

and     object_id(A.TABLE_NAME) = @TableObjectId

 

— Grab the first column since we have no key column declared

if(len(@KeyOrderColumn) < 1 or @KeyOrderColumn is null)

begin

    set @KeyOrderColumn = ( select name from sys.syscolumns where id = @TableObjectId and colorder = 1)

end

 

set @TableRows = (select rows from sys.partitions where object_id = @TableObjectId and index_id < 2)

 

if(@PageSize > @TableRows)

    begin

        set @StrSQL = ‘select ‘ + @TableSelectColumns + ‘ from ‘ + @TableName + ‘ order by ‘ + @KeyOrderColumn

    end

if(@PageSize < @TableRows)

    begin

set @StrSQL = ‘select ‘ + @TableSelectColumns

+ ‘, RowNumber into #Results from (select *, row_number() over(order by ‘

+ @KeyOrderColumn + ‘ ) as RowNumber from ‘

+ @TableName    + ‘) SortTable where RowNumber > ‘

+ cast(@PageRowStart as nvarchar) + ‘ and RowNumber < ‘

+ cast(@PageRowEnd as nvarchar)

        + ‘ alter table #Results drop column RowNumber ‘

        + ‘ select * from #Results’

    end    

 

exec sp_executesql @StrSQL

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.