真正通用分页过程

http://www.cnblogs.com/sgm/archive/2009/09/28/1575644.html

找了很多网上通用的分页存储过程，或多或少都有缺陷，由于项目中使用的分页地方比较多，现推出功能强大的分页过程，该过程根据网上流传 (nzperfect [no_mIss] 高效通用分页存储过程)的分页过程改版的，希望大家一起来测试完善，把分页进行到底，创建一个功能、效率强大的通用分页过程
1.支持多表查询
2.支持多字段排序，无需设置排序类型
3.支持分组
4.支持复杂查询
5.有些复杂查询可能会有问题，需要调整自己的SQL

declare @tables VARCHAR(2000),--支持多表名，表1 a,表2 b where a.编码=b.编码
@fields VARCHAR(2000),        --显示列名，如果是全部字段则为*
@pkfield VARCHAR(100),        --单一主键或唯一值键,支持,隔开组合字段
@strwhere VARCHAR(2000),      --查询条件 不含'where'字符，如id>10 and LEN(userid)>9
@orderfield VARCHAR(500),     --排序技持多字段排序,不含'order by'字符，如id asc,userid desc
@pagesize INT,                --每页输出的记录数
@pageindex INT,               --当前页数
@strgroup VARCHAR(500),       --汇总字段
@rowcount INT                 --记录总数 0:会返回总记录

SET @tables='employee a'
SET @fields='a.*'
SET @pkfield='a.emp_id'
SET @strwhere=''
SET @orderfield=''
SET @rowcount=0
SET @pagesize=20
SET @pageindex=2
SET @strgroup=''

SET NOCOUNT ON
IF ISNULL(@rowcount,'') = '' OR @rowcount < 0 SET @rowcount = 0
SET @orderfield = RTRIM(LTRIM(@orderfield))
SET @pkfield = RTRIM(LTRIM(@pkfield))
IF @pagesize < 0 SET @pagesize=20
IF ISNULL(@pageindex,0) < 0 SET @pageindex = 1

WHILE CHARINDEX(', ',@orderfield) > 0 OR CHARINDEX(' ,',@orderfield) > 0
BEGIN
  SET @orderfield = REPLACE(@orderfield,', ',',')
  SET @orderfield = REPLACE(@orderfield,' ,',',')
END

IF ISNULL(@tables,'') = '' OR ISNULL(@fields,'') = '' OR ISNULL(@pkfield,'') = '' RETURN
DECLARE @new_where1 VARCHAR(1000),@new_where2 VARCHAR(1000), @new_order1 VARCHAR(1000),@new_order2 VARCHAR(1000), @strsql NVARCHAR(4000),@pagecount int,@sort_type INT --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
DECLARE @i_index INT,@i_length INT,@strfield_r VARCHAR(100),@strtemp VARCHAR(500),@strorder_r1 VARCHAR(500),@strorder_r2 VARCHAR(500),@strorder_r3 VARCHAR(500),@strpkfield_r VARCHAR(100)

--设置排序顺序及排序字段--
IF ISNULL(@orderfield,'') = '' AND CHARINDEX(',',@pkfield)=0
  SET @sort_type='1'
ELSE IF CHARINDEX(',',@orderfield)>0 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
  SET @sort_type='3'
  IF CHARINDEX('ASC', @orderfield)=0 OR CHARINDEX('DESC', @orderfield)=0
  BEGIN
    SET @strtemp = @orderfield + ','
    SET @orderfield = ''   
    SET @i_index = CHARINDEX(',',@strtemp)
    WHILE @i_index > 0
    BEGIN
      SET @strfield_r = LEFT(@strtemp, @i_index-1)
      SET @orderfield = @orderfield + CASE WHEN @orderfield='' then '' else ',' end + @strfield_r + ' ASC'
      SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
      SET @i_index = CHARINDEX(',',@strtemp)
    END    
  END
  IF CHARINDEX(',',@pkfield) = 0
  BEGIN
    IF CHARINDEX(@pkfield, @orderfield) = 0 SET @orderfield = @orderfield + ','+@pkfield+' ASC'
  END
  ELSE
  BEGIN
    SET @strtemp = @pkfield + ','
    SET @i_index = CHARINDEX(',',@strtemp)
    WHILE @i_index>0
    BEGIN
      SET @strfield_r = LEFT(@strtemp, @i_index-1)
      IF CHARINDEX(@strfield_r, @orderfield) = 0 SET @orderfield = @orderfield + ',' + @strfield_r + ' ASC'
      SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
      SET @i_index = CHARINDEX(',',@strtemp)
    END
  END
END
ELSE --单列排序,需要区分是否存在主键字段
BEGIN
  IF CHARINDEX(',',@pkfield)>0 --主键中存在多字段
  BEGIN
    SET @sort_type='3'
    IF LTRIM(@orderfield)<>'' AND (CHARINDEX('ASC', @orderfield)=0 OR CHARINDEX('DESC', @orderfield)=0) SET @orderfield = @orderfield + ' ASC'

    SET @strtemp = @pkfield + ','
    SET @i_index = CHARINDEX(',',@strtemp)
    WHILE @i_index>0
    BEGIN
      SET @strfield_r = LEFT(@strtemp, @i_index-1)
      IF CHARINDEX(@strfield_r, @orderfield) = 0 SET @orderfield = @orderfield + CASE WHEN @orderfield='' then '' else ',' end + @strfield_r + ' ASC'
      SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
      SET @i_index = CHARINDEX(',',@strtemp)
    END
  END
  ELSE  --字键中只存在一个字段
  BEGIN
    SELECT @sort_type=CASE WHEN CHARINDEX('DESC',@orderfield)>0 THEN '2' ELSE '1' END
    IF LTRIM(@orderfield)<>''
    BEGIN
      IF CHARINDEX(@pkfield, @orderfield) = 0
      BEGIN
        SET @sort_type='3'
        SELECT @orderfield = @orderfield + CASE WHEN CHARINDEX('DESC',@orderfield)>0 OR CHARINDEX('ASC',@orderfield)>0 THEN '' ELSE ' ASC' END + ',' + @pkfield + ' ASC'
      END
    END
  END
END
--设置排序顺序及排序字段--

IF(@pkfield<>'' and @pageindex>1)
BEGIN
  SET @strtemp = @pkfield
  SET @i_index = CHARINDEX('.',@strtemp)
  WHILE @i_index>0
  BEGIN
    SET @strfield_r = LEFT(@strtemp, @i_index)
    SET @strpkfield_r = REPLACE(@pkfield,@strfield_r,'TMP.')
    SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 then LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
    SET @i_index = CHARINDEX('.',@strtemp)
  END
END

IF ISNULL(@strwhere,'') = ''
BEGIN
  SELECT @new_where1 = ' '
  SELECT @new_where2 = CASE WHEN CHARINDEX('where',@tables)>0 THEN ' AND ' ELSE ' WHERE  ' END + ' '
END
ELSE
BEGIN
  SELECT @new_where1 = CASE WHEN CHARINDEX('where',@tables)>0 then ' AND ' ELSE ' WHERE  ' END + @strwhere
  SELECT @new_where2 = CASE WHEN CHARINDEX('where',@tables)>0 then ' AND ' ELSE ' WHERE  ' END + @strwhere + ' AND '
END

IF ISNULL(@orderfield,'') = '' or @sort_type = 1  OR @sort_type = 2
BEGIN
  IF @sort_type = 1
  BEGIN
    SET @new_order1 = ' ORDER BY ' + @pkfield + ' ASC'
    SET @new_order2 = ' ORDER BY ' + @pkfield + ' DESC'
    SET @strorder_r1 = ' ORDER BY ' + @strpkfield_r + ' ASC'
    SET @strorder_r2 = ' ORDER BY ' + @strpkfield_r + ' DESC'
  END
  IF @sort_type = 2
  BEGIN
    SET @new_order1 = ' ORDER BY ' + @pkfield + ' DESC'
    SET @new_order2 = ' ORDER BY ' + @pkfield + ' ASC'
    SET @strorder_r1 = ' ORDER BY ' + @strpkfield_r + ' DESC'
    SET @strorder_r2 = ' ORDER BY ' + @strpkfield_r + ' ASC'
  END
END

IF @sort_type = 3 --AND  CHARINDEX(','+@pkfield+' ',','+@orderfield)>0
BEGIN
  SET @new_order1 = @orderfield
  SET @new_order2 = @orderfield + ','
  SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
  SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
  IF(@new_order1<>'' and @pageindex>1)
  BEGIN
    SET @strtemp = @new_order1
    SET @i_index = CHARINDEX('.',@strtemp)
    WHILE @i_index>0
    BEGIN
      SET @strfield_r = LEFT(@strtemp, @i_index)
      SET @strorder_r1 = REPLACE(@new_order1,@strfield_r,'TMP.')
      SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 THEN LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
      SET @i_index = CHARINDEX('.',@strtemp)
    END
  END

  IF(@new_order2<>'' and @pageindex>1)
  BEGIN
    SET @strtemp = @new_order2
    SET @i_index = CHARINDEX('.',@strtemp)
    WHILE @i_index>0
    BEGIN
      SET @strfield_r = LEFT(@strtemp, @i_index)
      SET @strorder_r2 = REPLACE(@new_order2,@strfield_r,'TMP.')
      SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 THEN LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
      SET @i_index = CHARINDEX('.',@strtemp)
    END
    SET @strorder_r2=LEFT(@strorder_r2,LEN(@strorder_r2)-1)
  END
  SET @new_order1 = ' ORDER BY ' + @orderfield
  SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
  SET @strorder_r1 = ' ORDER BY ' + @strorder_r1
  SET @strorder_r2 = ' ORDER BY ' + @strorder_r2
END

IF @rowcount = 0
BEGIN
  SET @strsql = 'select @rowcount=count(1) from (select 1 val from ' + @tables + @new_where1 + @strgroup+' ) tab_sel'
  EXEC SP_EXECUTESQL @strsql,N'@rowcount INT OUTPUT',@rowcount OUTPUT
END
set @pagecount = CEILING((@rowcount+0.0)/@pagesize)
IF @pageindex > @pagecount SET @pageindex =  @pagecount

IF @pageindex = 1 OR @pageindex >= @pagecount
BEGIN
  IF @pageindex = 1 --返回第一页数据
    SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ @fields + ' FROM '+ @tables + @new_where1 + @strgroup + @new_order1
  ELSE  --返回最后一页数据
    SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + ' * FROM ('+ 'SELECT TOP ' + CONVERT(VARCHAR(10),ABS(@pagesize*@pageindex-@rowcount-@pagesize))+ ' ' + @fields + ' FROM '+ @tables + @new_where1 + @strgroup + @new_order2 + ' ) AS TMP '+ @strorder_r1
END
ELSE
BEGIN
  IF @sort_type = 1  --仅主键正序排序
  BEGIN
    IF @pageindex <= @pagecount/2  --正向检索
    BEGIN
      SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + @fields + ' FROM '+ @tables + @new_where2 + @pkfield + ' > ' + '(SELECT MAX(TBPkField) FROM (SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize*(@pageindex-1)) + ' ' + @pkfield + ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order1 +' ) AS TMP) ' + @strgroup + @new_order1
    END
    ELSE  --反向检索
    BEGIN
      SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize)+ ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + ' * FROM (' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' < ' + '(SELECT MIN(TBPkField) FROM (SELECT TOP '
           + CONVERT(VARCHAR(10),@rowcount-@pagesize*@pageindex) + ' ' + @pkfield + ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order2 +' ) AS TMP) ' + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1
    END
  END
  ELSE IF @sort_type = 2  --仅主键反序排序
  BEGIN
     IF @pageindex <= @pagecount/2  --正向检索
     BEGIN
       SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' '+CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' < ' + '(SELECT MIN(TBPkField) FROM (SELECT TOP '
              + CONVERT(VARCHAR(10),@pagesize*(@pageindex-1)) + ' ' + @pkfield +' as TBPkField FROM '+ @tables + @new_where1 + @strgroup + @new_order1 + ') AS TMP) '+ @strgroup + @new_order1
     END
     ELSE  --反向检索
     BEGIN
       SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM (' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' > ' + '(SELECT MAX(TBPkField) FROM (SELECT TOP ' + CONVERT(VARCHAR(10),@rowcount-@pagesize*@pageindex) + ' ' + @pkfield
                + ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order2 +' ) AS TMP) ' + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1
     END
  END
  ELSE IF @sort_type = 3  --多列排序
  BEGIN
    IF @pageindex <= @pagecount/2  --正向检索
    BEGIN
      SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' '+CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM ( ' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' * FROM ( ' + ' SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize*@pageindex) + ' ' + @fields
               + ' FROM ' + @tables + @new_where1 + @strgroup + @new_order1 + ' ) AS TMP ' + @strorder_r2 + ' ) AS TMP ' + @strorder_r1
    END
    ELSE  --反向检索
    BEGIN
      SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM ( ' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' * FROM ( '
               + ' SELECT TOP ' + CONVERT(VARCHAR(10),@rowcount-@pagesize *@pageindex+@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where1 + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1 + ' ) AS TMP ' + @strorder_r1
    END
  END
END
EXEC(@strsql)
print @strsql
GO