CREATE PROCEDURE SelectPagedSQL
(
@SQL nvarchar(512),
@IndexField nvarchar(100),
@PageSize int=10,
@PageIndex int=1,
@Sort nvarchar(128)=@IndexField,
@TotalCount int=0 output
)
AS
declare @strSQL nvarchar(1024)
set nocount on
set @strSQL='
select @TotalCount=count(*) from ('+@SQL+') as t '
exec sp_executesql
@strSQL,
N'@TotalCount int=0 OUTPUT',
@TotalCount=@TotalCount OUTPUT
declare @ItemCount int
set @ItemCount=@TotalCount-@PageSize*@PageIndex
if(@ItemCount<0)
set @ItemCount=@ItemCount+@PageSize
else
set @ItemCount=@PageSize
if(@ItemCount<0)return 1
set @strSQL='SELECT * FROM
(SELECT TOP '+str(@ItemCount)+' * FROM
(SELECT TOP '+str(@PageSize*@PageIndex)+' * FROM
('+@SQL+') AS t0
ORDER BY '+@IndexField+' ASC) AS t1
ORDER BY '+@IndexField+' DESC) AS t2
ORDER BY ' +@Sort
exec sp_executesql
@strSQL
GO