Hello all,

After benefitting from the code examples and explanations above, I thought I'd make a contribution.

Below is a generic stored procedure that will retrieve the page of rows based on the parameters fed to it. It determines whether the table called for is in fact a table or a view. If it's a table, it uses the sys-tables to get the total row count (thanks to a post above this one!) and if it's a view, it uses the original strategy of calculating the inverse row # for a given row. I didn't compute the total rows because I thought whatever was calling it would only have to make the computation once rather than once-per-row if I had done it here.

Before the Efficiency Police hall me away, this was slapped together as a proof of concept and is not (in any way) presented as the best way of doing this. And there is a rule. Your ORDER-BY clause must include ASC where appropriate. This made inverting the order-by merely ugly, down from absolutely hideous.

[Code]

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[GetPage]

-- Add the parameters for the stored procedure here

(

@TableName varchar(1000)

,@Fields varchar(1000)

,@OrderBy varchar(1000)

,@StartPosition int

,@PageLen int

)

AS

Begin

DECLARE

@strSQL varchar(max)

,@RowCount int

,@RightNow DATETIME

,@TableType varchar(20)

,@RevOrderBy varchar(1000)

Set @RightNow=GETDATE()

Set @PageLen = @PageLen - 1

if not EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = @TableName)

begin

Select 'Table/View not found' as Error

return (-1)

end

select @TableType = TABLE_TYPE from INFORMATION_SCHEMA.tables where table_name = @TableName

if @TableType = 'BASE TABLE'

begin

SELECT @RowCount=P.Rows

FROM sys.partitions P

INNER JOIN sys.indexes I ON (P.object_id = I.object_id) AND (P.index_id = I.index_id)

INNER JOIN sys.objects O ON (P.object_id = O.object_id)

WHERE (I.type IN(0,1)) AND (O.name = PARSENAME(@TableName, 1) AND

O.[schema_id] = IsNull(SCHEMA_ID(PARSENAME(@TableName, 2)), O.[schema_id]))

ORDER BY O.name

set @strSQL =' SELECT * ' +

', ' + convert(varchar(10),@RowCount) + ' as [RowCount] ' +

' FROM (SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber ' +

' FROM [' + @TableName + ']) as [' + @TableName + '_A] ' +

' WHERE RowNumber BETWEEN ' + convert(varchar(10),@StartPosition) +

' AND ' + convert(varchar(10),(@StartPosition + @PageLen)) + ' ORDER BY RowNumber'

end

else

begin

set @RevOrderBy = upper(@OrderBy) + ','

set @RevOrderBy = replace(replace(replace(@RevOrderBy ,' ASC,',' ~A,'),' DESC,',' ASC,'),' ~A',' DESC')

set @RevOrderBy = left(@RevOrderBy ,len(@RevOrderBy)-1)

set @strSQL =' SELECT *' +

' FROM (' +

'SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber, ' +

' ROW_NUMBER() OVER(ORDER BY ' + @RevOrderBy + ') AS RowNumberInverse ' +

' FROM [' + @TableName + ']) as [' + @TableName + '_A] ' +

' WHERE RowNumber BETWEEN ' + convert(varchar(10),@StartPosition) +

' AND ' + convert(varchar(10),(@StartPosition + @PageLen)) + ' ORDER BY RowNumber'

end

--SELECT SQL=@strSQL

EXEC(@strSQL)

End

[/code]

Enjoy!