[转]分页存储过程，可支持多表查询，任意排序

  1
  2
  3说明：1.支持多表查询 2.支持任意排序 3.不支持表别名
  4参考了
  5    evafly920:[分享]千万数量级分页存储过程（效果演示）
  6    地址：http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx 
  7
  8
  9IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
 10    DROP PROCEDURE usp_PagingLarge
 11GO
 12*/
 13
 14CREATE PROCEDURE usp_PagingLarge
 15@Tables VARCHAR(200),         --表名，可以是多个表，但不能用别名
 16@Identity VARCHAR(50),         --主键，可以为空，但@Order为空时该值不能为空
 17@Fields    VARCHAR(200),         --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
 18@PageSize INT,             --每页记录数
 19@Page INT,             --当前页，0表示第1页
 20@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
 21@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by
 22@Order VARCHAR(100) = ''    --排序，可以为空，为空默认按主键升序排列，不用填 order by
 23AS
 24BEGIN
 25    DECLARE @SortColumn VARCHAR(50)
 26    DECLARE @Operator CHAR(2)
 27    DECLARE @SortTable VARCHAR(50)
 28    DECLARE @SortName VARCHAR(50)
 29    IF @Fields = ''
 30         SET @Fields = '*'
 31    IF @Filter = ''
 32         SET @Filter = 'WHERE 1=1'
 33    ELSE
 34         SET @Filter = 'WHERE ' +  @Filter
 35    IF @Group <>''
 36         SET @Group = 'GROUP BY ' + @Group
 37
 38    IF @Order <> ''
 39    BEGIN
 40         DECLARE @pos1  INT, @pos2 INT
 41         SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
 42         IF CHARINDEX(' DESC', @Order) > 0 AND CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
 43             SET @Operator = '<='
 44         ELSE
 45             SET @Operator = '>='
 46         SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
 47         SET @pos1 = CHARINDEX(',', @SortColumn)
 48         IF @pos1 > 0
 49             SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
 50         SET @pos2 = CHARINDEX('.', @SortColumn)
 51         IF @pos2 > 0
 52         BEGIN
 53             SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
 54             IF @pos1 > 0 
 55                 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
 56             ELSE
 57                 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
 58         END
 59         ELSE
 60         BEGIN
 61             SET @SortTable = @Tables
 62             SET @SortName = @SortColumn
 63         END
 64    END
 65    ELSE
 66    BEGIN
 67         SET @SortColumn = @Identity
 68         SET @SortTable = @Tables
 69         SET @SortName = @SortColumn
 70         SET @Order = @SortColumn
 71         SET @Operator = '>='
 72    END
 73
 74    DECLARE @type varchar(50)
 75    DECLARE @prec int
 76    SELECT @type=t.name, @prec=c.prec
 77    FROM sysobjects o 
 78    JOIN syscolumns c on o.id=c.id
 79    JOIN systypes t on c.xusertype=t.xusertype
 80    WHERE o.name = @SortTable AND c.name = @SortName
 81    IF CHARINDEX('char', @type) > 0
 82    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
 83
 84    DECLARE @TopRows INT
 85    SET @TopRows = @PageSize * @Page + 1
 86    EXEC('
 87        DECLARE @SortColumnBegin  ' + @type + '
 88        SET ROWCOUNT  ' + @TopRows + '
 89        SELECT @SortColumnBegin= ' + @SortColumn + ' FROM  ' + @Tables + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
 90        SET ROWCOUNT  ' + @PageSize + '
 91        SELECT  ' + @Fields + ' FROM  ' + @Tables + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + 
@Group + ' ORDER BY ' + @Order + '    
 92    ')    
 93END
 94
 95GO
 96
 97--调用例子：
 98    --1.单表/单排序
 99    EXEC usp_PagingLarge 'bigtable','d_id','d_id,d_title,d_content,d_time',20,0,'','','d_id'
100    --2.单表/多排序
101    EXEC usp_PagingLarge 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
102    --3.多表/单排序
103    EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ', 20, 0, '', '', 'bigtable.d_id asc'
104    --4.多表/多排序
105    EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc '<#input id="av_Plugin_Type_input" type="hidden" value="-1" />