sql 2000 分页存储过程

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go

--  [SelectBase] 1,1,'( select j.*,u.P_Name from Job j left join Users u on j.UserID=u.ID) T','Flag=0'
             alter   procedure   [ SelectBase ]
             @PageIndex                 int , 
             @PageSize                 int , 
             @TableName      nvarchar ( 2000 ), 
             @Where          nvarchar ( 2000 ) = ''  
             as  
             Declare   @rowcount            int  
             Declare   @intStart            int  
             Declare   @intEnd           int  
             declare   @Column1   varchar ( 32 )  -- 第一列名称
             Declare   @SQl   nvarchar ( 2000 ),  @WhereR   nvarchar ( 1000 ),  @OrderBy   nvarchar ( 1000 ) 
             set   @rowcount = 0  
             set  nocount  on  
             if   @Where <> ''  
             begin  
             set   @Where = '  and  ' + @Where  
             end  
             if   CHARINDEX ( ' order by ' ,  @Where ) > 0  
             begin  
             set   @WhereR = substring ( @Where ,  1 ,  CHARINDEX ( ' order by ' , @Where ) - 1 )     -- 取得条件 
             set   @OrderBy = substring ( @Where ,  CHARINDEX ( ' order by ' , @Where ),  Len ( @Where ))     -- 取得排序方式(order by 字段 方式) 
             end  
             else  
             begin  
             set   @WhereR = @Where  
             set   @OrderBy = '  order by id asc '  
             end  
             set   @SQl = ' SELECT @rowcount=count(*) from  ' + cast ( @TableName   as   nvarchar ( 2000 )) + '  where 1=1  ' + @WhereR  
            
             exec  sp_executeSql  @SQl ,N ' @rowcount int output ' , @rowcount  output 
             if   @PageIndex = 0   and   @PageSize = 0      -- 不进行分页,查询所有数据列表 
             begin  
             set   @SQl = ' SELECT * from  ' + cast ( @TableName   as   nvarchar ( 2000 )) + '  where 1=1  ' + @Where  
             end  
             else      -- 进行分页查询数据列表 
             begin  
             set   @intStart = ( @PageIndex - 1 ) * @PageSize + 1 ; 
             set   @intEnd = @intStart + @PageSize - 1  
             declare   @PKName   nvarchar ( 50 )
             if ( len ( @TableName ) > 50 )
             begin
                 Set   @Column1 = ' ID '
             end
             else
             begin
             set   @Column1 = col_name ( object_id ( @tableName ), 1 )  -- 设置第一列名称
             end
             set   @SQl = ' Create table #tem(Row int identity(1,1) not null,joinRow int)  '   
             set   @SQl = @SQl + ' insert #tem(joinRow) select  ' + @Column1 + '  from  ' + @TableName   + '  where 1=1  ' + @WhereR  
             set   @SQl = @SQl + '  select * from  ' + @TableName + '  right join #tem  on  ' + @Column1 + ' =#tem.joinRow  '   
             set   @SQl = @SQl + '  where #tem.Row between  ' + cast ( @intStart   as   varchar ) + '  and  ' + cast ( @intEnd   as   varchar ) 
             end  
             -- PRINT @SQl
             exec  sp_executeSql  @SQl  
             return   @rowcount  

            set nocount off 

版权声明：本文原创发表于博客园，作者为路过秋天，原文链接：http://www.cnblogs.com/cyq1162/archive/2009/08/13/1545028.html