分页存储过程

-- Add the parameters for the stored procedure here<br>-- =============================================<br><br>-- ALTER date: <2007-05-30><br>-- Description: <读取数据记录数量><br>-- =============================================<br>CREATE PROCEDURE ComPagerCount(<br> @Tables varchar(50), --表<br> @PrimaryKey varchar(50), --主键<br> @Filter varchar(800) = NULL, --:条件过滤，不带 Where<br> @Sort varchar(200) = Null, --:排序，不带 order by<br> @PageSize int, --分页大小<br> @CurrentPage int, --分页索引<br> @docount bit --if true 则仅读取总记录数<br>)<br>as<br>Declare @sCondit varchar(800)<br>Declare @sOrder varchar(200)<br>if @Filter IS NOT NULL AND @Filter != ''<br> SET @sCondit = ' WHERE ' + @Filter<br>else<br> SET @sCondit = ''<br>if @Sort IS Null And @Sort = ''<br> Set @sOrder = ' ORDER By '+ @PrimaryKey<br>else<br> Set @sOrder = ' ORDER By '+ @Sort<br>BEGIN<br> Begin Tran<br> set nocount on<br> IF(@docount=1)<br> exec('select count('+ @PrimaryKey +') from '+ @Tables + @sCondit)<br> ELSE<br> begin<br> declare @PageLowerBound int<br> declare @PageUpperBound int<br> set @PageLowerBound=(@CurrentPage-1)*@PageSize<br> set @PageUpperBound=@PageLowerBound+@PageSize<br> create table #pageindex(id int identity(1,1) not null,nid int)<br> set rowcount @PageUpperBound<br> exec('insert into #pageindex(nid)<br> select '+ @PrimaryKey +' from '+ @Tables + @sCondit + @sOrder )<br><br> Exec('SELECT O.* FROM '+ @Tables +' O,#pageindex p WHERE O.'<br> + @PrimaryKey +'=p.nid and p.id>'+ @PageLowerBound +' and p.id<='+ @PageUpperBound +' order by p.id')<br> END<br> set nocount off<br> If (@@Error=0) Commit Tran<br> Else ROLLBACK Tran<br>END<br><br><br><br>GO<br><br><br><br><br>/*<br>***************************************************************<br>** 千万数量级分页存储过程 **<br>***************************************************************<br>参数说明:<br>1.Tables :表名称,视图<br>2.PrimaryKey :主关键字<br>3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc<br>4.CurrentPage :当前页码<br>5.PageSize :分页尺寸<br>6.Filter :过滤语句，不带Where <br>7.Group :Group语句,不带Group By<br><br>***************************************************************/<br>CREATE PROCEDURE ComPagerData(<br> @Tables varchar(1000),<br> @PrimaryKey varchar(100),<br> @Sort varchar(200) = NULL,<br> @CurrentPage int = 1,<br> @PageSize int = 10,<br> @Fields varchar(1000) = '*',<br> @Filter varchar(1000) = NULL,<br> @Group varchar(1000) = NULL<br>)<br>AS<br>/*默认排序*/<br>IF @Sort IS NULL OR @Sort = ''<br>SET @Sort = @PrimaryKey<br><br>DECLARE @SortTable varchar(100)<br>DECLARE @SortName varchar(100)<br>DECLARE @strSortColumn varchar(200)<br>DECLARE @operator char(2)<br>DECLARE @type varchar(100)<br>DECLARE @prec int<br><br>/*设定排序语句.*/<br>IF CHARINDEX('DESC',@Sort)>0<br>BEGIN<br> SET @strSortColumn = REPLACE(@Sort, 'DESC', '')<br> SET @operator = '<='<br> END<br> ELSE<br> BEGIN<br> IF CHARINDEX('ASC', @Sort) > 0<br> SET @strSortColumn = REPLACE(@Sort, 'ASC', '')<br> SET @operator = '>='<br>END<br><br><br>IF CHARINDEX('.', @strSortColumn) > 0<br>BEGIN<br> SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))<br> SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))<br>END<br>ELSE<br>BEGIN<br> SET @SortTable = @Tables<br> SET @SortName = @strSortColumn<br>END<br><br>SELECT @type=t.name, @prec=c.prec<br>FROM sysobjects o <br> JOIN syscolumns c on o.id=c.id<br> JOIN systypes t on c.xusertype=t.xusertype<br>WHERE o.name = @SortTable AND c.name = @SortName<br><br>IF CHARINDEX('char', @type) > 0<br> SET @type = @type + '(' + CAST(@prec AS varchar) + ')'<br><br>DECLARE @strPageSize varchar(50)<br>DECLARE @strStartRow varchar(50)<br>DECLARE @strFilter varchar(1000)<br>DECLARE @strSimpleFilter varchar(1000)<br>DECLARE @strGroup varchar(1000)<br><br>/*默认当前页*/<br>IF @CurrentPage < 1<br>SET @CurrentPage = 1<br><br>/*设置分页参数.*/<br>SET @strPageSize = CAST(@PageSize AS varchar(50))<br>SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))<br><br>/*筛选以及分组语句.*/<br>IF @Filter IS NOT NULL AND @Filter != ''<br> BEGIN<br> SET @strFilter = ' WHERE ' + @Filter + ' '<br> SET @strSimpleFilter = ' AND ' + @Filter + ' '<br> END<br>ELSE<br> BEGIN<br> SET @strSimpleFilter = ''<br> SET @strFilter = ''<br> END<br>IF @Group IS NOT NULL AND @Group != ''<br>SET @strGroup = ' GROUP BY ' + @Group + ' '<br>ELSE<br>SET @strGroup = ''<br><br>/*执行查询语句*/<br>EXEC(<br>'<br>DECLARE @SortColumn ' + @type + '<br>SET ROWCOUNT ' + @strStartRow + '<br>SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '<br>SET ROWCOUNT ' + @strPageSize + '<br>SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '<br>'<br>)<br><br><br>GO<br>