分页存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter   PROCEDURE PdbPage
/*
***************************************************************
**  千万数量级分页存储过程  come from -- http://www.chinait.com.cn/blogs/servucn/archive/2005/11/29/278.aspx
***************************************************************
参数说明:
1.Tables             :表名称,视图
2.PrimaryKey         :主关键字
3.Sort               :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage        :当前页码
5.PageSize           :分页尺寸
5.Fields      :列名
6.Filter             :过滤语句，不带Where
7.Group             :Group语句,不带Group By
--exec PdbPage  'TWebArticleBuffer','id','id ASC','2','50','ishtml=1',''
***************************************************************/
(@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
 print '111111111111'
 /*默认排序*/
 IF @Sort IS NULL OR @Sort = ''
  SET @Sort = @PrimaryKey
 print + @sort
 DECLARE @SortTable varchar(100)
 DECLARE @SortName varchar(100)
 DECLARE @strSortColumn varchar(200)
 DECLARE @operator char(2)
 DECLARE @type varchar(100)
 DECLARE @prec int
 print '222222'
 /*设定排序语句.*/
 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
 print + @strSortColumn
  print '33333333333333'
 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
  print '444444444444'

 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) + ')'
  print @type
  print '55555555555'
 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
  print '666666666'
 /*设置分页参数.*/
 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 = ''
  print '77777777777'
 /*执行查询语句*/
 --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 + ' ')
 declare @str varchar(1000)
print '----------------'
 print 'type= ' + @type --
 print 'strstartrow=' + @strstartrow
 print + @strSortColumn --
print = ' + @Tables
print = ' + @strFilter --
print = ' + @strGroup --
print = ' + @Sort
print = ' + @strPageSize
print = ' + @Fields
print = ' + @strSortColumn --
print = ' + @operator
print = ' + @strSimpleFilter --
print '---------------------'
 set @str = '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 + ' '
 print @str

/*
--select top 50 * from cityadmin.TWebArticleBuffer where (ishtml=0) and id not in(select top 100 id from cityadmin.TWebArticleBuffer where ishtml=0 order by id) order by id

exec PdbPage  'TWebArticleBuffer','id','id ASC','2','50','UserName,Title','ishtml=1',''

SELECT  t.name, c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = 'TWebArticleBuffer' AND c.name = 'id'

DECLARE @SortColumn int
  SET ROWCOUNT 51
SELECT @SortColumn=id  FROM TWebArticleBuffer  ORDER BY id ASC
SET  ROWCOUNT 50
SELECT * FROM TWebArticleBuffer WHERE id >= @SortColumn   ORDER BY id ASC 

DECLARE @SortColumn int
SET ROWCOUNT 51 ]
SELECT @SortColumn=id  FROM TWebArticleBuffer  ORDER BY id ASC
SET ROWCOUNT 50
SELECT ishtml=1 FROM TWebArticleBuffer WHERE id >= @SortColumn   ORDER BY id ASC 

DECLARE @SortColumn int
  SET ROWCOUNT 51
  SELECT @SortColumn=id
   FROM TWebArticleBuffer WHERE ishtml=1 
   ORDER BY id ASC
   SET ROWCOUNT 50 SELECT UserName,Title
   FROM TWebArticleBuffer WHERE id >= @SortColumn  AND ishtml=1   ORDER BY id ASC 

 

select * from TWebArticleBuffer
*/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--select top 50 * from cityadmin.TWebArticleBuffer where (ishtml=0) and id not in(select top 100 id from cityadmin.TWebArticleBuffer where ishtml=0 order by id) order by id

exec PdbPage  'TWebArticleBuffer','id','id ASC','2','50','UserName,Title','ishtml=1',''

SELECT  t.name, c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = 'TWebArticleBuffer' AND c.name = 'id'

DECLARE @SortColumn int
  SET ROWCOUNT 51
SELECT @SortColumn=id  FROM TWebArticleBuffer  ORDER BY id ASC
print @sortcolumn
select * from TWebArticleBuffer where id = 3073

SET  ROWCOUNT 50
SELECT * FROM TWebArticleBuffer WHERE id >= @SortColumn   ORDER BY id ASC 

DECLARE @SortColumn int
SET ROWCOUNT 51
SELECT @SortColumn=id  FROM TWebArticleBuffer  ORDER BY id ASC
print @sortcolumn
SET ROWCOUNT 50
SELECT ishtml=1 FROM TWebArticleBuffer WHERE id >= @SortColumn   ORDER BY id ASC 

DECLARE @SortColumn int
  SET ROWCOUNT 51
  SELECT @SortColumn=id
   FROM TWebArticleBuffer WHERE ishtml=1 
   ORDER BY id ASC
   SET ROWCOUNT 50 SELECT UserName,Title
   FROM TWebArticleBuffer WHERE id >= @SortColumn  AND ishtml=1   ORDER BY id ASC 

 

set rowcount 50
select * from TWebArticleBuffer where id > 3800