CREATE PROCEDURE [dbo].[Pro_GetPageOfRecords]
@PageSize INT=20, --分页大小
@CurrentPage INT, --第几页
@Clumns VARCHAR(1000)='*', --需要得到的字段
@TableName VARCHAR(100), --需要查询的表
@Condition VARCHAR(1000)='', --查询条件，不用加Where关键字
@AscColumn VARCHAR(100)='', --排序的字段名(即order by column asc/desc)
@BitOrderType BIT=0, --排序类型(0为升序,1为降序)
@PkColumn VARCHAR(50)='', --主键名称,不可为空
@TotalCount INT OUTPUT , --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
BEGIN
DECLARE @strSql VARCHAR(5000) --分页语句
DECLARE @strOrderType VARCHAR(1000) --排序类型语句
DECLARE @SqlCount NVARCHAR(4000) --记录数语句
DECLARE @new_where VARCHAR(1000) --查询条件
----Where条件处理-------------
IF @Condition!=''
BEGIN
SET @new_where = ' Where ' + @Condition
END
ELSE
BEGIN
SET @new_where = ' Where 1=1'
END
----------总记录数查询---------------
SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+STR(@PageSize)+') FROM (Select * FROM ' + @TableName + @new_where+') AS T'
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
--------------排序处理------------
IF @BitOrderType=1 --降序
BEGIN
IF @AscColumn!=''
SET @strOrderType=' ORDER BY '+@AscColumn+' DESC'
ELSE
SET @strOrderType=' ORDER BY '+@PkColumn+' DESC'
END
ELSE
BEGIN
IF @AscColumn!=''
SET @strOrderType=' ORDER BY '+@AscColumn+' ASC'
ELSE
SET @strOrderType=' ORDER BY '+@PkColumn+' ASC'
END
-----------------分页处理--------------------
IF @CurrentPage=1 --第一页
BEGIN
SET @strSql='SELECT TOP '+STR(@PageSize)+''+@Clumns+' FROM'+ @TableName + @new_where+@strOrderType
END
ELSE --其他页
BEGIN
set @strsql = 'SELECT TOP '+STR(@PageSize)+' '+@Clumns+' FROM '+ @TableName + @new_where+' AND ('+@PkColumn+' NOT IN (SELECT TOP '+STR((@PageSize-1)*@PageSize)+' '+@PkColumn+' FROM '+@TableName+''+@Condition+@strOrderType+'))'+@strOrderType
END EXEC (@strSql)
END