改写了下通用存储过程分页,支持多表关联查询与排序./******************************************************获取分页数据的通用存储过程,支持多表关联查询 **作者:蔡鸟非菜**根据sql语句：**select table1.*,table2.* into #tblTemp from table1,table2 where .. order by id**select top 10 * from #tblTemp where id >= (select max(id) from (select top ((页码-1)*页大小) id from #tblTemp)**Function dbo.TrimColumn(@orderName) 去除字段的前缀 例如 @str = dbo.TrimColumn('A.ID') 则@str为'ID' *****************************************************/Create PROCEDURE GetDataPage ( @tblName varchar(255), --表名 例如:set @tblName = 'table1 A,table2 B' @strGetFields varchar(1000), --要显示的列的名称 例如:set @strGetFields = 'A.ID,A.条码,B.仓库名称' @orderName varchar(255), --用来排序的字段名称 例如:set @orderName = 'A.ID' @orderType bit, --设置排序类型，升序0，其他降序 例如:set @orderType = '0' @pageSize int, --页显示的数据条数 例如:set @pageSize = '10' @pageIndex int, -- 页码 例如:set @pageIndex = '1' @doCount bit, --返回记录总数，非则返回 @strWhere varchar(1500) --查询条件 例如:set @strWhere = 'and A.仓库ID = B.ID' )AS declare @strSQL varchar(8000) -- 存放主SQL语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @DoCount != 0 --需要返回记录总数时 begin if @strWhere != '' set @strSQL = 'select count(*) as Total from ' + @tblName + ' where 1 = 1 ' + @strWhere else set @strSQL = 'select count(*) as Total from ' + @tblName + '' end else --不需要返回记录总数时 begin if @orderType != '0' begin set @strTmp = '<= (select min' set @strOrder = ' order by ' + @orderName +' desc' end else begin set @strTmp = '>= (select max' set @strOrder = ' order by ' + @orderName +' asc' end -- 查询的如果是第一页时 if @pageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top '+ str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' where 1 = 1 ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top '+ str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' '+ @strOrder end else --不是第一页时 begin if @orderType != '0' begin set @strOrder = ' order by ' + dbo.TrimColumn(@orderName) +' desc' end else begin set @strOrder = ' order by ' + dbo.TrimColumn(@orderName) +' asc' end set @strSQL = 'select ' + @strGetFields + ' into #tblTemp from ' + @tblName + ' where 1 = 1 ' + @strWhere + ' ' + @strOrder + ' select top ' + str(@PageSize) + ' * from [#tblTemp] ' + ' where [' + dbo.TrimColumn(@orderName) + '] ' + @strTmp + '(['+ dbo.TrimColumn(@orderName) + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + dbo.TrimColumn(@orderName) + '] from [#tblTemp]) as tblTmp) ' + @strOrder + ' drop table #tblTemp ' end end exec(@strSQL) RETURN
