SQL存储过程实现分页

第一种：简单的分页实现

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2    --指定显示的页码
SET @pagesize = 3   --每页的条数
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score[字段] DESC, speaker[字段]) AS rownum, *
      FROM SpeakerStats[表名]) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

第二种：多表联合的分页查询

三张表：Discuss_Table

Talk_Table

User_Table

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2    --指定显示的页码
SET @pagesize = 3   --每页的条数
SELECT * 
FROM(SELECT ROW_NUMBER() over(ORDER BY D_id DESC) AS rownum,a.*,b.*,c.* 
	   FROM(Discuss_Table AS a 
	        INNER JOIN Talk_Table as b 
			ON a.Titleid=b.T_id)
			JOIN User_Table as c 
			ON a.User_id=c.U_id 
			WHERE c.UserName='zhangfugui')as tb
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

第三种：完整的分页查询

CREATE PROCEDURE SP_PAGE 
@TableName varchar(50),          --表名 
@Fields varchar(5000) = '*',     --字段名(全部字段为*) 
@OrderField varchar(5000),       --排序字段(必须支持多字段) 
@sqlWhere varchar(5000) = Null,  --条件语句(不需要附加where) 
@pageSize int,                   --每页多少条记录 
@pageIndex int = 1 ,             --指定当前为第几页 
@TotalPage int output,           --返回条数 
@OrderType bit                   --设置排序类型,1 升序 0 值则降序 
AS 
BEGIN 
declare @strOrder varchar(400)   --排序类型 

BEGIN Tran                       --开始事务 
	--执行SQL语句拼接
	Declare @sqlquery nvarchar(4000); 
	Declare @totalRecord int; 
	--计算总记录数 
	--如果没有条件语句
	IF (@SqlWhere='' or @sqlWhere is NULL) 
	SET @sqlquery = 'select @totalRecord = count(*) from ' + @TableName
	ELSE 
	SET @sqlquery = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere 
	--执行查询数据操作
	EXEC sp_executesql @sqlquery,N'@totalRecord int OUTPUT',@totalRecord OUTPUT   --计算总记录数 

	--计算总页数 
    SET @TotalPage = Ceiling((@totalRecord+0.0)/@pagesize) --计算页总数 

    IF @OrderType = 0   --如果@OrderType是0,就执行降序 
	BEGIN
	SET @strOrder = ' order by [' + @OrderField +'] desc' 
	END
	ELSE                --升序排列
	BEGIN 
	SET @strOrder = ' order by [' + @OrderField +'] asc' 
	END 

	IF (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) 
	SET @sqlquery = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName 
	ELSE 
	SET @sqlquery = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere 
	
	--处理页数超出范围情况 
	IF @PageIndex<=0 
	SET @pageIndex = 1 
	
	IF @pageIndex>@TotalPage 
	SET @pageIndex = @TotalPage 

	--处理开始点和结束点 
	Declare @StartRecord int 
	Declare @EndRecord int 
	
	--开始节点为1
	SET @StartRecord = (@pageIndex-1)*@PageSize + 1 
	--最后节点为页面节点数
	SET @EndRecord = @StartRecord + @pageSize - 1 

	IF @OrderType = 0 
	BEGIN 
	SET @strOrder = ' order by rowid desc' 
	--如果@OrderType是0，就执行降序，这句很重要！
	END 
	ELSE 
	BEGIN 
	SET @strOrder = ' order by rowid asc' 
	END 
	--继续合成sql语句 
	SET @sqlquery = @sqlquery + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + ' '+@strOrder 
	-- 执行SQL语句 @sqlquery 
	Exec(@sqlquery) 
--------------------------------------------------- 
	IF @@Error <> 0 
	BEGIN
	ROLLBACK Tran 
	RETURN -1 
	END 
	ELSE 
	BEGIN 
	COMMIT Tran 
	RETURN @totalRecord ---返回记录总数 
	End 
END