存储过程分页

不支持多表,不支持嵌套,晕,也敢拿出来.给个哥以前写的真正通用的给你看

/***************************************************************
分页存储过程
Create BY Tellov 2008-05-28
[e-mail]
注意:使用本存储过程,SQL语句中必须要带有Order BY 子句

支持多表链接查询,支持别名,支持嵌套SELECT语句
注:语句中如果带有group by ,则页总数和记录数无法正确统计

-- Modify BY Tellov 2008-12-10 修正了多字段排序且序不同时分页会出现返回数据集不正确的问题
-- Moidfy By Tellov 2009-08-03 修正了出现此种嵌套(select *,(select count(1) from b) c from a)时此存储过程会报错的BUG
***************************************************************
参数说明:
1.@sql         查询语句
2.@CurrentPage   当前页
3.@PageSize     页大小
4.@Counts     查询到的记录总数
5.@pageCount   页总数
***************************************************************/
Alter PROCEDURE [dbo].[Sp1_Exec_Page]
(
  @Sql nvarchar(4000),           -- 要执行的SQL语句
  @CurrentPage int = 1,         -- 当前页
  @PageSize int = 30,           -- 每页纪录数
  @counts int output,           -- 纪录总数
  @pageCount int output         -- 页总数
)
--WITH ENCRYPTION
AS

Set Lock_TimeOut 2000

declare @sort nvarchar(500)
declare @variableName nvarchar(300)
declare @setVariableName nvarchar(2000)
declare @where nvarchar(4000)
declare @temp nvarchar(max)
declare @tempwhere nvarchar(2000)
select @sql = replace(@sql,' ',' ')
select @where = ''
select @setVariableName = ''
select @variableName = ''


-- 取出orderBy
if CHARINDEX(' order by ',@sql) = 0
begin
  select '使用此存储过程时查询语句中必须带上order by 子句';
end
else
begin
  declare @wherelocation int,@orderbylocation int,@groupbylocation int

  -- 取最后一个order by 的位置
  select @orderbylocation = CHARINDEX(' order by ',@sql)
  while CHARINDEX(' order by ',@sql,@orderbylocation＋10) <> 0
  begin
    select @orderbylocation = CHARINDEX(' order by ',@sql,@orderbylocation＋10)
  end

  -- 默认当前页
  IF @CurrentPage < 1
    SET @CurrentPage = 1

  -- 获取总行数
  select @temp = left(@Sql,@orderbylocation)
  select @temp = 'Select @counts = count(1) from ( ' ＋ @temp ＋ ') as __tt'
  --print @temp
  exec sp_executesql @temp,N'@counts int output',@counts output

  -- 获取总分页数
  If @counts < @pageSize
    Select @pageCount = 1
  else
    Select @pageCount = ((@Counts-1) / @pageSize) ＋1

  -- 提高效率,如果是第一页直接返回
  if @CurrentPage = 1
  begin
    declare @returntemp nvarchar(4000)
    select @returntemp = 'select top ' ＋ convert(nvarchar(20),@pageSize) ＋ substring(@sql,CHARINDEX('select ',@sql) ＋ 6,len(@sql))
    exec(@returntemp)
    --print @returntemp
  end
  else
  begin    
    -- 取最后一个WHERE的位置
    select @wherelocation = CHARINDEX(' where ',@sql)
    while @wherelocation <> 0 and CHARINDEX(' on ',@sql,@wherelocation ＋ 7) <> 0
    begin
        select @wherelocation = CHARINDEX(' where ',@sql,@wherelocation ＋ 7)
    end

    -- 取最后一个group by 的位置
    select @groupbylocation = CHARINDEX(' group by ',@sql)
    while CHARINDEX(' group by ',@sql,@groupbylocation＋10) <> 0
    begin
        select @groupbylocation = CHARINDEX(' group by ',@sql,@groupbylocation＋10)
    end

    select @sort = substring(@sql,@orderbylocation＋10,len(@sql))

    -- 生成执行SQL时必须的语句
    select @sort = @sort ＋ ','
    select @tempwhere = ''
    while CHARINDEX(',',@sort) > 0
    begin
        select @temp = substring(@sort,0,CHARINDEX(',',@sort))
        select @temp = replace(@temp,' desc','')
        select @temp = replace(@temp,' asc','')
        select @setVariableName = @setVariableName ＋ '@' ＋ ltrim(replace(@temp,'.','')) ＋ '=Convert(nvarchar(300),' ＋ @temp ＋ ',21),'
        select @variableName = @variableName ＋ '@' ＋ ltrim(replace(@temp,'.','')) ＋ ' nvarchar(300),'
        --print @variableName
        if @tempwhere <> ''
        begin
          select @where = @where ＋ ' or (' ＋ right(@tempwhere,len(@tempwhere)-4)
        end

        select @where = @where ＋ ' and ' ＋ @temp
        if CHARINDEX(' desc',@sort) <> 0
        begin
          select @where = @where ＋ ' <= @' ＋ ltrim(replace(@temp,'.',''))
        end
        else
        begin
          select @where = @where ＋ ' >= @' ＋ ltrim(replace(@temp,'.',''))
        end
       
        if @tempwhere <> ''
        begin
          select @where = @where ＋ ')'
        end

        select @tempwhere = @tempwhere ＋ ' and ' ＋ @temp ＋ ' = @' ＋ ltrim(replace(@temp,'.',''))

        select @sort = substring(@sort,CHARINDEX(',',@sort)＋1,len(@sort))
    end

    select @variableName = left(@variableName,len(@variableName)-1)
    select @setVariableName = left(@setVariableName,len(@setVariableName)-1)
    select @where = '(' ＋ substring(@where,6,len(@where))＋ ')'

    --print @where
    --print @setVariableName
    --print @variableName

    -- 设置RowCount
    declare @strStartRow int
    Select @strStartRow = ((@CurrentPage - 1)*@PageSize ＋ 1)
    Set RowCount @strStartRow

    --print @sql

    -- 最终执行Sql
    select @temp = 'DECLARE ' ＋ @variableName ＋ '
    SET ROWCOUNT ' ＋ Convert(nvarchar(20),@strStartRow) ＋ '
    Select ' ＋ @setVariableName ＋ ' from (' ＋ left(@Sql,@orderbylocation) ＋ ') as t ' ＋ substring(@sql,@orderbylocation,len(@sql)) ＋ '
    SET ROWCOUNT ' ＋ Convert(nvarchar(20),@PageSize) ＋ '
    '
    --print @temp
    --print @sql
    --print @wherelocation

    if @wherelocation <> 0
        select @temp = @temp ＋ STUFF(@sql,@wherelocation＋7,0,@where ＋ ' and ')
    else
    begin
        if @groupbylocation <> 0
          select @temp = @temp ＋ STUFF(@sql,@groupbylocation ＋ 1,0,' where ' ＋ @where ＋ ' ')
        else if @orderbylocation <> 0
          select @temp = @temp ＋ STUFF(@sql,@orderbylocation ＋ 1,0,' where ' ＋ @where ＋ ' ')
    end

    exec(@temp)

    --print @temp
  end
end