分页存储过程集

http://focus755.com/article5.asp?article_id=956 SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create   PROCEDURE sh_page /* *************************************************************** **   中国无忧商务网千万数量级分页存储过程                      ** *************************************************************** 参数说明: 1.Tables              :表名称,视图 2.PrimaryKey          :主关键字 3.Sort                :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc 4.CurrentPage         :当前页码 5.PageSize            :分页尺寸 6.Filter              :过滤语句，不带Where 7.Group              :Group语句,不带Group By 版权所有：中国无忧商务网(http://www.cn5135.com) 欢迎试用，有什么心得请 Mail:xzhijun@GMail.com 示例代码：http://www.cn5135.com/Opportunities/Pagination.asp 注：转载请保留相关版权说明，谢谢！^_^ ***************************************************************/ ( @Tables varchar(1000), @PrimaryKey varchar(100), @Sort varchar(200) = NULL, @CurrentPage int = 1, @PageSize int = 10, @Fields varchar(1000) = '*', @Filter varchar(1000) = NULL, @Group varchar(1000) = NULL ) AS /*默认排序*/ IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int /*设定排序语句.*/ IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<=' END ELSE BEGIN IF CHARINDEX('ASC', @Sort) = 0 SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0     SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strFilter varchar(1000) DECLARE @strSimpleFilter varchar(1000) DECLARE @strGroup varchar(1000) /*默认当前页*/ IF @CurrentPage < 1 SET @CurrentPage = 1 /*设置分页参数.*/ SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50)) /*筛选以及分组语句.*/ IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = '' /*执行查询语句*/ EXEC( ' DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' SET ROWCOUNT ' + @strPageSize + ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ' ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO   /*    经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：    exec GetRecordFromPage news,newsid,10,100000    news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。 */   /*    函数名称: GetRecordFromPage    函数功能: 获取指定页的数据    参数说明: @tblName       包含数据的表名             @fldName       关键字段名             @PageSize      每页记录数             @PageIndex     要获取的页码             @OrderType     排序类型, 0 - 升序, 1 - 降序             @strWhere      查询条件 (注意: 不要加 where)    作　　者: 铁拳    邮　　箱: sunjianhua_kki@sina.com    创建时间: 2004-07-04    修改时间: 2004-07-04 */ CREATE PROCEDURE GetRecordFromPage      @tblName       varchar(255),        -- 表名      @fldName       varchar(255),        -- 字段名      @PageSize      int = 10,            -- 页尺寸      @PageIndex     int = 1,             -- 页码      @OrderType     bit = 0,             -- 设置排序类型, 非 0 值则降序      @strWhere      varchar(2000) = ''   -- 查询条件 (注意: 不要加 where) AS declare @strSQL    varchar(6000)        -- 主语句 declare @strTmp    varchar(1000)        -- 临时变量 declare @strOrder varchar(500)         -- 排序类型 if @OrderType != 0 begin      set @strTmp = '<(select min'      set @strOrder = ' order by [' + @fldName + '] desc' end else begin      set @strTmp = '>(select max'      set @strOrder = ' order by [' + @fldName +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' * from ['      + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['      + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['      + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'      + @strOrder if @strWhere != ''      set @strSQL = 'select top ' + str(@PageSize) + ' * from ['          + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['          + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['          + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '          + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin      set @strTmp = ''      if @strWhere != ''          set @strTmp = ' where (' + @strWhere + ')'      set @strSQL = 'select top ' + str(@PageSize) + ' * from ['          + @tblName + ']' + @strTmp + ' ' + @strOrder end exec (@strSQL) GO     实现数据分页(1) /* 作者:郭新行 功能:实现数据分页 日期:2004-11-11 描述:此过程功能实现对数据的分页,输入的参数中 @SelectFieldName,@tbName,@strWhere,       @OrderFieldName 合起来为一个完整的Select查询语句,此语句中不能包含中间表       @OrderFieldName 必须是可以作为主键的字段 */ ALTER        PROCEDURE usp_GetRecordFromPage      @tblName        varchar(1000),         -- 表名      @SelectFieldName     varchar(4000),               -- 要显示的字段名(不要加select)      @strWhere        varchar(4000),               -- 查询条件(注意: 不要加 where)      @OrderFieldName       varchar(255),                -- 排序索引字段名      @PageSize        int ,                  -- 页大小      @PageIndex       int = 1,                   -- 页码      @iRowCount       int output,                  -- 返回记录总数      @OrderType       bit = 0                   -- 设置排序类型, 非 0 值则降序             AS DECLARE @strSQL     VARCHAR(4000)        -- 主语句 DECLARE @strTmp     VARCHAR(4000)        -- 临时变量 DECLARE @strOrder   VARCHAR(1000)        -- 排序类型 DECLARE @strRowCount     NVARCHAR(4000)       -- 用于查询记录总数的语句 SET @OrderFieldName=ltrim(rtrim(@OrderFieldName)) SET @SelectFieldName=ltrim(rtrim(@SelectFieldName)) SET @strWhere =ltrim(rtrim(@strWhere)) IF @SelectFieldName='' BEGIN      SET @SelectFieldName=' * ' END IF @OrderType != 0 BEGIN      SET @strTmp = '<(select min'      SET @strOrder = ' order by ' + @OrderFieldName +' desc' END ELSE BEGIN      SET @strTmp = '>(select max'      SET @strOrder = ' order by ' + @OrderFieldName +' asc' END IF @strWhere != '' BEGIN      SET @strSQL = 'select top ' + ltrim(rtrim(str(@PageSize)))+' ' + @SelectFieldName+' from '          + @tblName + ' where ' + @OrderFieldName + @strTmp + '('          + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + ltrim(rtrim(str((@PageIndex-1)*@PageSize)))+' '          + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' '          + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder END ELSE BEGIN      SET @strSQL = 'select top ' + ltrim(rtrim(str(@PageSize)))+' ' + @SelectFieldName+' from '          + @tblName + ' where ' + @OrderFieldName + @strTmp + '('          + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + ltrim(rtrim(str((@PageIndex-1)*@PageSize)))+' '          + @OrderFieldName + ' from ' + @tblName   + @strOrder + ') as tblTmp)'          + @strOrder END IF @PageIndex = 1 BEGIN      SET @strTmp = ''      IF @strWhere != ''      BEGIN          SET @strTmp = ' where ' + @strWhere      END      SET @strSQL = 'select top ' + ltrim(rtrim(str(@PageSize)))+ ' ' + @SelectFieldName+' from '                   + @tblName + @strTmp + ' ' + @strOrder END PRINT(@strSQL) EXEC(@strSQL)    IF @strWhere!='' BEGIN    SET @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere END ELSE BEGIN    SET @strRowCount = 'select @iRowCount=count(*) from ' + @tblName END EXEC sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out <%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> <% '分页SQL语句生成代码 Function GetPageSQL(tblName,fldName,PageSize,PageIndex,OrderType,strWhere) Dim strTemp,strSQL,strOrder      '根据排序方式生成相关代码     if OrderType=0 then          strTemp=">(select max([" & fldName & "])"          strOrder=" order by [" & fldName & "] asc"     else          strTemp="<(select min([" & fldName & "])"          strOrder=" order by [" & fldName & "] desc"      end if           '若是第1页则无须复杂的语句     if PageIndex=1 then          strTemp=""         if strWhere<>"" then              strTmp = " where " + strWhere          end if          strSQL = "select top " & PageSize & " * from [" & tblName & "]" & strTmp & strOrder     else        '若不是第1页，构造SQL语句          strSQL="select top " & PageSize & " * from [" & tblName & "] where [" & fldName & "]" & strTemp & _         " from (select top " & (PageIndex-1)*PageSize & " [" & fldName & "] from [" & tblName & "]"         if strWhere<>"" then              strSQL=strSQL & " where " & strWhere          end if          strSQL=strSQL & strOrder & ") as tblTemp)"         if strWhere<>"" then              strSQL=strSQL & " And " & strWhere          end if          strSQL=strSQL & strOrder      end if      GetPageSQL=strSQL        '返回SQL语句 End Function response.write(GetPageSQL("message","messageid",10,1,0,"")) %>