+ ' ' + @ID + ' from ' + @tblName
+ ' order by ' + @fldSort + ' ' + @strFSortType +
') AS TBMinID)'
+ ' order by ' + @fldSort + ' ' + @strFSortType
END
ELSE
BEGIN
SET @strTmp = @SqlSelect + ' top ' + CAST(@pageSize AS VARCHAR(4))
+ ' ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' >(select max(' + @ID + ') from (' +
@SqlSelect + ' top ' + CAST(@pageSize * (@page -1) AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName
+ ' order by ' + @fldSort + ' ' + @strFSortType +
') AS TBMinID)'
+ ' order by ' + @fldSort + ' ' + @strFSortType
END
END
END
ELSE
BEGIN
SET @page = @pageIndex -@page + 1 --后半部分数据处理
IF @page <= 1
--最后一页数据显示
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@lastcount AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' order by ' + @fldSort + ' ' + @strSortType +
') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType
ELSE
IF @Sort = 1
BEGIN
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' where ' + @ID + ' >(select max(' + @ID + ') from(' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -2) + @lastcount AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName
+ ' order by ' + @fldSort + ' ' + @strSortType +
') AS TBMaxID)'
+ ' order by ' + @fldSort + ' ' + @strSortType +
') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType
END
ELSE
BEGIN
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' where ' + @ID + ' <(select min(' + @ID + ') from(' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -2) + @lastcount AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName
+ ' order by ' + @fldSort + ' ' + @strSortType +
') AS TBMaxID)'
+ ' order by ' + @fldSort + ' ' + @strSortType +
') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType
END
END
END
ELSE
--有查询条件
BEGIN
IF @pageIndex < 2
OR @page <= @pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
BEGIN
IF @page = 1
SET @strTmp = @SqlSelect + ' top ' + CAST(@pageSize AS VARCHAR(4))
+ ' ' + @fldName + ' from ' + @tblName
+ ' where 1=1 ' + @strCondition + ' order by ' + @fldSort +
' ' + @strFSortType
ELSE
IF (@Sort = 1)
BEGIN
SET @strTmp = @SqlSelect + ' top ' + CAST(@pageSize AS VARCHAR(4))
+ ' ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' <(select min(' + @ID + ') from (' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -1) AS VARCHAR(20)) +
' ' + @ID + ' from ' + @tblName
+ ' where (1=1) ' + @strCondition + ' order by ' + @fldSort
+ ' ' + @strFSortType + ') AS TBMinID)'
+ ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType
END
ELSE
BEGIN
SET @strTmp = @SqlSelect + ' top ' + CAST(@pageSize AS VARCHAR(4))
+ ' ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' >(select max(' + @ID + ') from (' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -1) AS VARCHAR(20)) +
' ' + @ID + ' from ' + @tblName
+ ' where (1=1) ' + @strCondition + ' order by ' + @fldSort
+ ' ' + @strFSortType + ') AS TBMinID)'
+ ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType
END
END
ELSE
BEGIN
SET @page = @pageIndex -@page + 1 --后半部分数据处理
IF @page <= 1
--最后一页数据显示
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@lastcount AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' where (1=1) ' + @strCondition + ' order by ' + @fldSort
+ ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort
+ ' ' + @strFSortType
ELSE
IF (@Sort = 1)
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' where ' + @ID + ' >(select max(' + @ID + ') from(' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -2) + @lastcount AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName
+ ' where (1=1) ' + @strCondition + ' order by ' + @fldSort
+ ' ' + @strSortType + ') AS TBMaxID)'
+ ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType
+ ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType
ELSE
SET @strTmp = @SqlSelect + ' * from (' + @SqlSelect + ' top ' +
CAST(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' +
@tblName
+ ' where ' + @ID + ' <(select min(' + @ID + ') from(' + @SqlSelect
+ ' top ' + CAST(@pageSize * (@page -2) + @lastcount AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName
+ ' where (1=1) ' + @strCondition + ' order by ' + @fldSort
+ ' ' + @strSortType + ') AS TBMaxID)'
+ ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType
+ ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType
END
END
------返回查询结果-----
EXEC sp_executesql @strTmp
--select datediff(ms,@timediff,getdate()) as 耗时
--print @strTmp
SET NOCOUNT OFF
GO
/*
*
* 调用示例：
* EXEC Proc_QB_Pager 'TB_Table','ID,Name,PWD',10,100000,'ID',0,null,'ID',0
*
*/
