sql server 2005分页存储过程和sql server 2000分页存储过程(转,来源不明)

sql server 2005分页存储过程和sql server 2000分页存储过程，sql 2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持jion的，sql2000的分页存储过程，也可以运行在sql2005上，但是性能没有sql2005的版本好。

 1USE [svnhost]
 2GO
 3/**//****** 对象:  StoredProcedure [dbo].[up_Page2005]    脚本日期: 05/21/2008 11:27:05 ******/
 4SET ANSI_NULLS ON
 5GO
 6SET QUOTED_IDENTIFIER ON
 7GO
 8CREATE proc [dbo].[up_Page2005] 
 9@TableName varchar(50),        --表名
@Fields varchar(5000) = '*',    --字段名(全部字段为*)
@OrderField varchar(5000),        --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int,                    --每页多少条记录
@pageIndex int = 1 ,            --指定当前为第几页
@TotalPage int output            --返回总页数 
as
begin
    Begin Tran --开始事务
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;    
    --计算总记录数

     if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数        

    --计算总页数

    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    

    --处理页数超出范围情况
    if @PageIndex<=0 
        Set @pageIndex = 1

    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int

    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --继续合成sql语句
    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     print @sql   
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
       End    
end

 

 1/**//****** 对象:  StoredProcedure [dbo].[up_Page2005V2]    脚本日期: 05/21/2008 11:27:15 ******/
 2SET ANSI_NULLS ON
 3GO
 4SET QUOTED_IDENTIFIER ON
 5GO
 6-- =============================================
 7-- Author:        <Author,,Name>
 8-- Create date: <Create Date,,>
 9-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_Page2005V2] 
@TableName varchar(50),        --表名
@Fields varchar(5000) = '*',    --字段名(全部字段为*)
@OrderField varchar(5000),        --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int,                    --每页多少条记录
@pageIndex int = 1 ,            --指定当前为第几页
@totalRecord int = 0,
@TotalPage int output            --返回总页数
AS
BEGIN

   Begin Tran --开始事务
     Declare @sql nvarchar(4000); 
    if @totalRecord<=0 begin
        --计算总记录数
        if (@SqlWhere='' or @sqlWhere=NULL)
            set @sql = 'select @totalRecord = count(*) from ' + @TableName
        else
            set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
        EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
    end

    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    

    --处理页数超出范围情况
    if @PageIndex<=0 
        Set @pageIndex = 1

    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --处理开始点和结束点

    Declare @StartRecord int
    Declare @EndRecord int

    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --继续合成sql语句
    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     print @sql   
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End   
END

GO

 

 1 /****** 对象:  StoredProcedure [dbo].[up_Page2005V2_Join]    脚本日期: 05/21/2008 11:27:30 ******/
 2 SET ANSI_NULLS ON
 3 GO
 4 SET QUOTED_IDENTIFIER ON
 5 GO
 6 -- =============================================
 7 -- Author:        <Author,,Name>
 8 -- Create date: <Create Date,,>
 9 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[up_Page2005V2_Join]
 @TableName varchar(150),        --表名
 @Fields varchar(5000) = '*',    --字段名(全部字段为*)
 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int = 1 ,            --指定当前为第几页
 @totalRecord int = 0,
 @TotalPage int output            --返回总页数
 AS
 
 BEGIN
      Begin Tran --开始事务
     Declare @sql nvarchar(4000); 
 
     if @totalRecord<=0 begin
 
         --计算总记录数
         if (@SqlWhere='' or @sqlWhere=NULL)
             set @sql = 'select @totalRecord = count(*) from ' + @TableName
         else
             set @sql = 'select @totalRecord = count(*) from ' + @TableName + '  where ' + @sqlWhere
         EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
     end
 
     --计算总页数
     select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
     if (@SqlWhere='' or @sqlWhere=NULL)
         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
     else
         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere    
 
     --处理页数超出范围情况
     if @PageIndex<=0 
         Set @pageIndex = 1
 
     if @pageIndex>@TotalPage
         Set @pageIndex = @TotalPage
 
      --处理开始点和结束点
     Declare @StartRecord int
     Declare @EndRecord int
 
     set @StartRecord = (@pageIndex-1)*@PageSize + 1
     set @EndRecord = @StartRecord + @pageSize - 1
 
     --继续合成sql语句
     set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
      print @sql 
 
     Exec(@Sql)
     ---------------------------------------------------
     If @@Error <> 0
       Begin
         RollBack Tran
         Return -1
       End
      Else
       Begin
         Commit Tran
         Return @totalRecord ---返回记录总数
       End   
 END
 

 

 

 1 USE [game]
 2 GO
 3 /****** 对象:  StoredProcedure [dbo].[page]    脚本日期: 05/21/2008 11:37:12 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 CREATE proc [dbo].[page]
 9 @RecordCount int output,
 @ReturnCount bit,
 @QueryStr nvarchar(1000)='table1',--表名、视图名、查询语句
 @PageSize int=20,  --每页的大小(行数)
 @PageCurrent int=2,  --要显示的页 从0开始
 @FdShow nvarchar (2000)='*', --要显示的字段列表
 @IdentityStr nvarchar (100)='id', --主键
 @WhereStr nvarchar (2000)='1=1',
 @FdOrder nvarchar(100)='desc' --排序    只能取desc或者asc
 as
 
 set nocount on
 declare
 @sql nvarchar(2000)
 
 if @WhereStr = '' begin
 
 set @WhereStr = '1=1'
 
 end
 
 if @ReturnCount=1 begin
 declare @tsql nvarchar(200)
 set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
 exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
 end
 
 if @PageCurrent = 0 begin
 set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
 end
 
 else begin
 if upper(@FdOrder) = 'DESC' begin
 
 set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'
 
 end
 
 else begin
 
 set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'
 
 end
 
 end
 
 --print @sql
 
 execute(@sql)
 
 --select @t = datediff(ms,@t1,getdate())---------------------