sql2005存储分页flex.doc

用于flex分组显示 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Author,,Name -- Create date: Create Date,, -- Description: Description,, -- ============================================= ALTER proc [dbo].[up_Pageinlei] @TableName varchar(50), --表名 @fenleiField varchar(50), --分类字段 @Fields varchar(5000) = *, --字段名(全部字段为*) @OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int = 1 , --指定当前为第几页 @TotalPage int output --返回总页数 as begin Begin Tran --开始事务 Declare @sql nvarchar(4000); --返回查询语句 Declare @totalRecord int; Declare @StartRecord int Declare @EndRecord int Declare @StartRecordStr varchar(50) Declare @EndRecordStr varchar(50) --计算总记录数 if (@SqlWhere= or @sqlWhere=NULL) set @sql = select @totalRecord = count( distinct +@fenleiField+) from + @TableName+ with(nolock) else set @sql = select @totalRecord = count( distinct +@fenleiField+) from + @TableName + with(nolock) where + @sqlWhere EXEC sp_executesql @sql,N@totalRecord int OUTPUT,@totalRecord OUTPUT--计算总记录数 --计算总页数 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) --处理页数超出范围情况 if @PageIndex=0 Set @pageIndex = 1 if @pageIndex@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 set @StartRecord = (@pageIndex-1)*@PageSize set @EndRecord = @StartRecord + @pageSize - 1 --取得主键值 set @sql = select @StartRecordStr = +@fenleiField+ from (select top +str(@StartRecord+1)+ * from (select distinct +@fenleiField+ from +@TableName+ )as d ) as a where +@fenleiField+ not in (select top +str(@StartRecord)+ * from (select distinct +@fenleiField+ from +@TableName+ ) as q ) order by yp_Code EXEC sp_executesql @sql,N@StartRecordStr varchar(50) OUTPUT,@StartRecordStr OUTPUT--计算总记录数 s