SQL存储过程实现分页

来源：互联网发布：狸窝dvd刻录软件编辑：程序博客网时间：2024/05/16 15:18

第一种：简单的分页实现

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

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

三张表：Discuss_Table

Talk_Table

User_Table

DECLARE @pagenum AS INT, @pagesize AS INTSET @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 tbWHERE 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 ' + @TableNameELSE 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,就执行降序 BEGINSET @strOrder = ' order by [' + @OrderField +'] desc' ENDELSE                --升序排列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 --开始节点为1SET @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 BEGINROLLBACK Tran RETURN -1 END ELSE BEGIN COMMIT Tran RETURN @totalRecord ---返回记录总数 End END

0 0