CREATE PROCEDURE [dbo].[Proc_Pagination]
@keyName varchar(255),
@tblName varchar(255),
@strGetFields varchar(1000) = '*',
@PageSize int = 10,
@PageIndex int = 1,
@PageCount int = 1 output,
@DataCount decimal = 1 output,
@SortName varchar(255)='',
@SortType varchar(255) = 'Asc',
@SearchCondition varchar(1500) = ''
AS
declare @strSQL Nvarchar(4000)
declare @strTmp varchar(110)
declare @strOrder varchar(400)
declare @seaOrder varchar(400)
begin
if @SearchCondition !=''
set @strSQL = 'select @DataCount=COUNT(*) from [' + @tblName + '] where '+@SearchCondition
else
set @strSQL = 'select @DataCount=COUNT(*) from [' + @tblName + ']'
EXEC SP_EXECUTESQL @strSQL,N'@DataCount int out ',@DataCount out
set @PageCount=(@DataCount+@PageSize-1)/@PageSize
if @PageIndex>@PageCount
SET @PageIndex=@PageCount
end
if @SortName!=''
begin
if @SortType = 'Desc'
begin
set @strOrder = ' order by [' + @SortName +'] Desc'
if @PageIndex*2<@PageCount
set @seaOrder = ' order by [' + @SortName +'] Desc'
else
set @seaOrder = ' order by [' + @SortName +'] Asc'
end
else
begin
set @strOrder = ' order by [' + @SortName +'] Asc'
if @PageIndex*2<@PageCount
set @seaOrder = ' order by [' + @SortName +'] Asc'
else
set @seaOrder = ' order by [' + @SortName +'] Desc'
end
if @PageIndex = 1
begin
if @SearchCondition != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @SearchCondition + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
end
else
begin
if @PageIndex*2<@PageCount
if @SearchCondition = ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] not in(select ['+ @keyName + '] from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + ']' + @seaOrder + ') as tblTmp)'+ @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] not in(select [' + @keyName + '] from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ' + @seaOrder + ') as tblTmp) and ' + @SearchCondition + ' ' + @strOrder
else
if @SearchCondition = ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] in(select ['+ @keyName + '] from (select top ' + str(@DataCount-(@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + ']' + @seaOrder + ') as tblTmp)'+ @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] in(select [' + @keyName + '] from (select top ' + str(@DataCount-(@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ' + @seaOrder + ') as tblTmp) and ' + @SearchCondition + ' ' + @strOrder
end
end
else
begin
if @PageIndex = 1
begin
if @SearchCondition != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @SearchCondition
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + ']'
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @keyName + '] >(select max(['+ @keyName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + '] ORDER BY ' + @keyName + ') as tblTmp)'
if @SearchCondition != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] >(select max([' + @keyName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ORDER BY ' + @keyName + ') as tblTmp) and ' + @SearchCondition
end
end
EXEC SP_EXECUTESQL @strSQL