Dynamic Data Paging

CREATE PROCEDURE dbo.spGetDataPage
(
@Table VarChar(128) = Null, 
@Columns VarChar(1000) = '*', 
@Criteria VarChar(2000) = Null,
@Group VarChar(2000) = Null,
@Sort VarChar(2000) = Null,
@PageNo Int = 1, 
@PageSize Int = 0, 
@GetTotals Bit = 0,
@TotalRecords Int = 0 Output, 
@TotalPages Decimal(10,2) = 0 Output
)
AS
-- Variables
Set NoCount On
Declare @AllRecords Bit, @GetDefaultSort Bit, @Criteria1 VarChar(8000), @Criteria2 VarChar(8000)
Declare @KeyColumn VarChar(8000), @PageNoStr VarChar(50), @PageSizeStr VarChar(50), @SkipRows VarChar(50)
Declare @Cursor Cursor, @Column VarChar(128), @Type VarChar(50), @MaxLen SmallInt

-- Set the Defaults
If (@PageNo < 1) Set @PageNo = 1
Select @KeyColumn = '', @Criteria1 = '', @Criteria2 = ''
Select @Criteria = Upper(@Criteria), @Sort = Upper(@Sort), @Group = Upper(@Group)
Select @Criteria = IsNull(@Criteria, ''), @Sort = IsNull(@Sort, ''), @Group = IsNull(@Group, '')
Select @PageNoStr = Convert(VarChar(50), @PageNo), @PageSizeStr = Convert(VarChar(50), @PageSize)
If (@Sort = '') Set @GetDefaultSort = 1

-- Determine the Key Columns from the Table
If (Select Count(*) From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where Xtype = 'pk'))) = 1
BEGIN
Select @KeyColumn = C.Column_Name 
From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where Xtype = 'pk'))
END
Else
BEGIN
Set @Cursor = Cursor Fast_Forward For
Select C.Column_Name As ColumnName, Data_Type As Type, IsNull(C.Character_Maximum_Length, C.Character_Octet_Length) As MaxLen
From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where Xtype = 'pk'))
Open @Cursor
Fetch Next From @Cursor Into @Column, @Type, @MaxLen
While (@@Fetch_Status <> -1)
BEGIN
If (@@Fetch_Status <> -2)
BEGIN
Set @KeyColumn = @KeyColumn + ' Convert(VarChar(50), ' + @Column + ') + '':'' +'
If (@GetDefaultSort = 1)
BEGIN
Set @Sort = @Sort + @Column + ', '
END
END
Fetch Next From @Cursor Into @Column, @Type, @MaxLen
END
Close @Cursor
DeAllocate @Cursor
Set @KeyColumn = SubString(@KeyColumn, 1, Len(@KeyColumn) - 7)
If (@GetDefaultSort = 1) Set @Sort = SubString(@Sort, 1, Len(@Sort) - 1)
END

-- Page Size
If (@PageSize Is Null) OR (@PageSize < 1)         -- Bring all records, don't do paging.
Set @AllRecords = 1
Else
BEGIN
Set @AllRecords = 0
    Set @SkipRows = Convert(VarChar(50), @PageSize * (@PageNo - 1))
END

-- Criteria
If (CharIndex('WHERE', @Criteria) > 0) Set @Criteria = Replace(@Criteria, 'WHERE', '')
If (@Criteria Is Not Null) AND (@Criteria <> '')
BEGIN
Set @Criteria1 = ' WHERE (' + @Criteria + ') '
Set @Criteria2 = ' AND (' + @Criteria + ') '
END

-- Sorting
If (@Sort Is Not Null) AND (@Sort <> '')
BEGIN
If (CharIndex('ORDER BY', @Sort) = 0) Set @Sort = ' ORDER BY ' + @Sort + ' '
END

-- Grouping
If (@Group Is Not Null) AND (@Group <> '')
BEGIN
If (CharIndex('GROUP BY', @Group) = 0) Set @Group = ' GROUP BY ' + @Group + ' '
END

-- Return the Records
If (@AllRecords = 1)                   -- Ignore paging and run a simple SELECT.
BEGIN
   EXEC ('Select ' + @Columns + ' From ' + @Table + ' ' + @Criteria1 + ' ' + @Group + ' ' + @Sort)
END
Else
BEGIN
If (@PageNo = 1)                                -- In this case we can execute a more efficient query with no subqueries.
BEGIN
EXEC (
'Select Top ' + @PageSizeStr + ' ' + @Columns + ' From ' + @Table + ' ' + @Criteria1 + ' ' + @Group + ' ' + @Sort
)
END
Else
BEGIN
EXEC (
'Select Top ' + @PageSizeStr + ' ' + @Columns + ' From ' + @Table
+ ' Where ' + @KeyColumn + ' NOT IN(Select Top ' + @SkipRows + ' ' + @KeyColumn + ' From ' + @Table + ' ' + @Criteria1 + ' ' + @Group + ' ' + @Sort + ')'
+ ' ' + @Criteria2 + ' ' + @Group + ' ' + @Sort
)
END
END

If (@GetTotals = 1)
BEGIN
Select @TotalRecords = Sum(Rows) From dbo.SysIndexes Where ID IN(Select ID From dbo.SysObjects Where Name = @Table)
If (@PageSize > 0) Set @TotalPages = Ceiling(@TotalRecords / @PageSize) + 1
END

RETURN