数据库分页存储过程，支持多字段排序

CREATE procedure Pro_Pagination( @tableName varchar(128), --表名 @fields varchar(4000)='*', --字段列表 @condition varchar(4000)='', --条件 @orderbyFields varchar(255), --排序的字段 支持多字段排序用","分开排序字段 @orderbyTypes varchar(10)='0', --默认是升序 010 @pageSize int=10, --页面大小 @pageIndex int=1, --所要访问的页索引 @currentPageIndex int=1 output, --实际的页索引 @recordCount int=0 output, --返回总记录数 @pageCount int=0 output --返回总页数 ) as begin declare @strSql nvarchar(4000) declare @strSql2 nvarchar(4000) declare @strOrderbyZ nvarchar(400) declare @strOrderbyF nvarchar(400) declare @strWhere nvarchar(400) declare @iLastPageRecordCount int declare @strFirstOrderbyField nvarchar(128) declare @strFirstOrderbyType nvarchar(1) declare @k int declare @i int declare @j int set @strSql = '' set @strSql2 = '' set @strOrderbyZ = '' set @strOrderbyF = '' set @strWhere = '' set @k = 1 set @j = 1 set @i = charindex(',',@orderbyFields,@j) set @strFirstOrderbyType = left(@strFirstOrderbyType,1) set @strFirstOrderbyField = @orderbyFields --由于支持多字段排序，且只能将第一个字段作为比较的条件 if @i > 0 begin set @strFirstOrderbyField = substring(@orderbyFields,@j,@i-@j) end --先计算出总记录数 set @strSql ='select @recordCount=count(*) from [' set @strSql =@strSql + @tableName +']' if(isnull(@condition,'')<>'') begin set @strSql =@strSql +' where '+ @condition end --得到总记录数 execute sp_executesql @strSql,N'@recordCount int output',@recordCount output --计算得到总页数 set @pageCount = cast( ceiling( 1.0 * @recordCount / @pageSize ) as int ) --计算出最后一页应显示的记录数 set @iLastPageRecordCount = @recordCount % @pageSize --生成排序字段的正反序字符串 while @i > 0 begin set @strOrderbyZ = @strOrderbyZ + substring(@orderbyFields,@j,@i-@j) set @strOrderbyZ = @strOrderbyZ + case substring(@orderbyTypes,@k,1) when '0' then ',' else ' desc,' end set @strOrderbyF = @strOrderbyF + substring(@orderbyFields,@j,@i-@j) set @strOrderbyF = @strOrderbyF + case substring(@orderbyTypes,@k,1) when '0' then ' desc,' else ',' end set @j = @i + 1 set @k = @k + 1 set @i =charindex(',',@orderbyFields,@j) end set @strOrderbyZ = @strOrderbyZ + substring(@orderbyFields,@j,len(@orderbyFields)) set @strOrderbyZ = @strOrderbyZ + case substring(@orderbyTypes,@k,1) when '0' then '' else ' desc' end set @strOrderbyF = @strOrderbyF + substring(@orderbyFields,@j,len(@orderbyFields)) set @strOrderbyF = @strOrderbyF + case substring(@orderbyTypes,@k,1) when '0' then ' desc' else '' end set @strOrderbyZ = ' order by ' +@strOrderbyZ set @strOrderbyF = ' order by ' +@strOrderbyF --生成条件字符串 if(isnull(@condition,'')<>'') begin set @strWhere = ' where ' +@condition + ' ' end -- set @strSql = 'select top ' +ltrim(str(@pageSize)) +' ' set @strSql = @strSql + @fields+ ' from ['+@tableName+'] ' set @strSql = @strSql + @strWhere --page索引的判断 if(@pageIndex<=1) begin --都显示第一页 set @currentPageIndex = 1 set @strSql = @strSql + @strOrderbyZ end else if(@pageIndex>=@pageCount) begin --倒序显示最后一页且只显示前@iLastPageRecordCount条记录 set @currentPageIndex = @pageCount set @strSql = 'select top '+ltrim(str(@iLastPageRecordCount)) +' ' set @strSql = @strSql + @fields + ' from [' +@tableName +'] ' set @strSql = @strSql + @strWhere + @strOrderbyF end else begin --显示相对应的页 set @currentPageIndex = @pageIndex if(isnull(@condition,'')<>'') begin set @strSql = @strSql +' and ' end else begin set @strSql = @strSql +' where ' end --将被排除显示的记录数 --做为条件时，只考虑第一个字段 set @k = @pageSize*(@pageIndex-1) set @strSql =@strSql +@strFirstOrderbyField +' not in (' set @strSql =@strSql +'select top '+ltrim(str(@k))+ ' ' + @strFirstOrderbyField +' ' set @strSql =@strSql +' from ['+@tableName+'] ' if(isnull(@condition,'')<>'') begin set @strSql =@strSql +'where '+ @condition end set @strSql =@strSql + @strOrderbyZ set @strSql =@strSql +') '+ @strOrderbyZ end -- print @strSql execute sp_executesql @strSql end GO