Binaryworld - Serverside Paging using dynamic T-SQL ... [ SQL Server -> Stored Procedure ]

This stored Procedure can page records for any specified query.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*

Description: Page a query. Query can have order by clause or it can pass separate orderby parameter

Examples:
=========================

[1] Default sort and default poagenumber/size
--------------------------------------------------------
exec sp_PageQuery 'select * from northwind.dbo.orders'

[2] Page#1 and Pagesize 30
--------------------------------------------------------
declare @a int
exec sp_PageQuery 'select * from northwind.dbo.orders order by customerid',NULL,1,30,@a out
Print 'Total Rows' + str(@a)

[3] Page#3 and Pagesize 30, order by orderid, customerid desc
--------------------------------------------------------
declare @b int
exec sp_PageQuery 'select * from northwind.dbo.orders','orderid,customerid desc',3,30,@b out
Print 'Total Rows' + str(@b)

*/
ALTER PROCEDURE sp_PageQuery
(
@Select varchar(8000),
@OrderBy varchar(2000)='',
@PageNum int=1,
@PageSize int=30,
@TotalRows int=0 OUTPUT
)

AS
BEGIN

declare @ColList varchar(8000);
declare @Where varchar(8000);
declare @i int;
declare @i2 int;
declare @tmp varchar(8000);
declare @dec varchar(8000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @sTmp varchar(2000)
declare @SQL varchar(8000);
declare @Sort varchar(2000);

declare @StartRow int;
declare @EndRow int;

set @StartRow = ((@PageNum-1)* @PageSize)+1
set @EndRow = @StartRow + @PageSize - 1

set @OrderBy=isnull(@OrderBy,'')
if ltrim(rtrim(@OrderBy))=''
set @OrderBy='1'

if @OrderBy='1'
begin
set @i = charindex('order by',@Select)
if @i>0
begin
set @OrderBy = ltrim(rtrim(right(@Select,len(@Select)-@i-8)))
   set @Select = left(@Select,@i-1)
end
end

create table #recCount(RecCount int)
exec('insert into #recCount (RecCount) select count(*) from ('+@Select+') a')
select @TotalRows=RecCount from #recCount
drop table #recCount

set @Sort = @OrderBy + ', '
set @dec = ''
set @Where = ''
set @SQL = ''

set @i = charindex(',' , @Sort)
while @i != 0
begin
set @tmp = left(@Sort,@i-1)
set @i2 = charindex(' ', @tmp)

set @f = case when @i2=0 then ltrim(rtrim(@tmp)) else ltrim(rtrim(left(@tmp,@i2-1))) end
set @d = case when @i2=0 then '' else ltrim(rtrim(substring(@tmp,@i2+1,100))) end

set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
set @i = charindex(',', @Sort)
set @symbol = case when @d = 'DESC' then '<' else '>' end +
    case when @i=0 then '=' else '' end

set @dec = @dec + 'declare @' + @f + ' sql_variant; '
set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
     @f + ' ' + @Symbol + ' @' + @f
set @Where = @Where + ' OR (' + @ColList + ') '
set @SQL = @SQL + ', @' + @f + '= ' + @f
end

set @SQL = @dec + ' ' +
   'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
   'SELECT ' + substring(@SQL,3,7500) + ' from (' + @Select + ') a ORDER BY ' +
   @OrderBy + '; ' + 'SET ROWCOUNT ' +
   convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
   'select * from (' + @Select + ') a WHERE ' +
   substring(@Where,4,7500) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'

exec(@SQL)
PRINT @SQL
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO