Created this one a while back and still use it. You might want to give it a try.
It aint as fancy as yours, but it will save you a lot of maintenance.
Since the user most of the time dictates the sortorder
and the where clause I prefer passing these in from the outside.
You can simply leave them blank where you don't need them.
CREATE procedure [dbo].[proc_GetSortedPage]
(
@TableName VARCHAR(500),
@SortClause VARCHAR(4000),
@WhereClause VARCHAR(4000),
@Pagesize int,
@Pageindex int
)
as
-- @TableName: name of table or view
-- @SortClause: the sort clause without the ORDER BY statement, but including ASC or DESC
-- @WhereClause: the where clause, without the WHERE statement
-- @Pagesize: the size of the page
-- @Pageindex: the number of the page you want to return, starts counting at 1
IF @SortClause IS NULL
set @SortClause = ''
set @SortClause = LTRIM(@SortClause)
set @SortClause = RTRIM(@SortClause)
if @SortClause <>''
set @SortClause = ' ORDER BY ' + @SortClause
IF @WhereClause IS NULL
set @WhereClause = ''
set @WhereClause = LTRIM(@WhereClause)
set @WhereClause = RTRIM(@WhereClause)
if @WhereClause <>''
set @WhereClause = ' WHERE ' + @WhereClause
if @pagesize is NULL
set @pagesize = 100
if @pagesize < 1
set @pagesize = 100
if @pageindex is null
set @pageindex = 1
if @pageindex < 1
set @pageindex = 1
declare @startrange varchar(15)
declare @endrange varchar(15)
set @startrange = cast((((@pageindex-1) * @pagesize) + 1) as varchar(15))
set @endrange = cast((@pageindex * @pagesize) as varchar(15))
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'WITH Records AS ('
SET @SQL = @SQL + 'SELECT ROW_NUMBER()'
SET @SQL = @SQL + ' OVER ('
if @SortClause <>''
SET @SQL = @SQL + @SortClause
SET @SQL = @SQL + ') '
SET @SQL = @SQL + ' AS Row, * '
SET @SQL = @SQL + ' FROM ' + @TableName
SET @SQL = @SQL + ' ' + @WhereClause
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT * FROM Records WHERE Row BETWEEN ' + @startrange + ' AND ' + @endrange + ' ' + @SortClause
--print @SQL
EXEC (@SQL);