- set ANSI_NULLS ON
- set QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Description: <高效分页存储过程,仅适用于Sql2005>
- -- Notes: <排序字段强烈建议建索引>
- -- =============================================
- ALTER Procedure [dbo].[up_Page2005]
- @TableName varchar(500), --表名
- @Fields varchar(8000) = '*', --字段名(全部字段为*)
- @OrderField varchar(8000), --排序字段(必须!支持多字段)
- @sqlWhere varchar(8000) = Null,--条件语句(不用加where)
- @pageSize int, --每页多少条记录
- @pageIndex int = 1 , --指定当前为第几页
- @TotalPage int output --返回总页数
- as
- begin
- Begin Tran --开始事务
- Declare @sql nvarchar(4000);
- Declare @totalRecord int;
- --计算总记录数
- if (@SqlWhere='' or @sqlWhere=NULL)
- set @sql = 'select @totalRecord = count(*) from ' + @TableName
- else
- set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
- EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
- --计算总页数
- select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
- if (@SqlWhere='' or @sqlWhere=NULL)
- set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
- else
- set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') 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
- set @StartRecord = (@pageIndex-1)*@PageSize + 1
- set @EndRecord = @StartRecord + @pageSize - 1
- --继续合成sql语句
- set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
- --print @sql ;
- Exec(@Sql)
- ---------------------------------------------------
- If @@Error <> 0
- Begin
- RollBack Tran
- Return -1
- End
- Else
- Begin
- Commit Tran
- Return @totalRecord ---返回记录总数
- End
- end
SQL SERVER 2005 通用高效分页存储过程
最新推荐文章于 2019-09-27 14:47:51 发布