分页存储过程(单表Sql2005)

ALTER PROCEDURE [dbo].[sp_bj_page]
    @CurrentPage int,
    @PageSize int,
  
    @Field_Info varchar(500),
    @Table_info varchar(1000),

    @Field_id varchar(10),    --
    @Field_Order varchar(100),--
 
    @otherwhere varchar(50),
 
    @RecordCount int output,
    @PageCount int output  
AS
BEGIN
    SET NOCOUNT ON;
 DECLARE @MinPage int
    DECLARE @MaxPage int
    DECLARE @sql varchar(1000)
    DECLARE @sqlt nvarchar(300)
 DEclare @Sqlt1 nvarchar(300)

 If @otherwhere='' or @otherwhere is null
 Begin
  set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_info
 End
 Else
 Begin
  set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_info + ' where '+ @otherwhere
 End
 
 --set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_info

    exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output       
 
    --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名  
 print 'recordcount'+convert(nvarchar,@RecordCount)

 Declare @Rownumber int
 Set @Rownumber=0

 If @otherwhere='' or @otherwhere is null
 Begin
  Set @Sqlt1 = 'select top 1 @Rownumber=cast(rownumber as int) 
  from (select * , row_number() over(order by syopid) as rownumber from ' + @Table_info + ' ) as a '
 End
 Else
 Begin
  Set @Sqlt1 = 'select top 1 @Rownumber=cast(rownumber as int) 
  from (select * , row_number() over(order by syopid) as rownumber from ' + @Table_info + ' ) as a ' +
  +' where '+ @otherwhere 
 End

 exec sp_executesql @Sqlt1,N'@Rownumber int output',@Rownumber output

 print @Rownumber
 
 If @Rownumber = 0
 Begin
  return 0
 End

    IF @PageSize <= 0  
        begin  
            set @PageSize = 10
        end  
 
    Else If @PageSize > @RecordCount
        begin  
            set @PageSize = @RecordCount
        end  
 
 Print 'PageSize' + convert(nvarchar,@PageSize)

    set @PageCount = @RecordCount / @PageSize  
 
    if ((@RecordCount % @PageSize) != 0)                    --如果除不净则加一页
        begin
            set @PageCount = @RecordCount / @PageSize
            set @PageCount = @PageCount + 1
        end  
    else 
        begin  
            set @PageCount = @RecordCount /@PageSize
        end  
 
 Print 'PageCount' + convert(nvarchar,@PageCount)

    IF @CurrentPage <= 0 
        begin  
            set @CurrentPage = 1
        end  
    else if @CurrentPage > @PageCount
        begin         
            set @CurrentPage = @PageCount       --如果输入页数大于总页数则符最后一页
        end  
 Print 'CurrentPage' + convert(nvarchar,@CurrentPage)
 

    SET @MinPage = (@CurrentPage - 1) * @PageSize + 1 + @Rownumber -1  
    SET @MaxPage = @MinPage + @PageSize - 1    
    print 'MaxPage' + convert(nvarchar,@MaxPage)
 
    BEGIN  
 
        if @Field_Info like '' 
 
            set @Field_Info = '*'
 
       if @otherwhere like '' 
    Begin 
            set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info +
      ' , row_number() over(order by ' + @Field_Order + ') as rownumber from '
          + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@MinPage)
          + ' and ' + convert(varchar(10),@MaxPage) 
    End
       else
    Begin 
            set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info +
        ' , row_number() over(order by '+ @Field_Order + ') as rownumber from '
         + @Table_info + ') as a where rownumber between '
        + convert(varchar(10),@MinPage) + ' and ' + convert(varchar(10),@MaxPage)
        + ' and ' + @otherwhere
    End 
    print (@sql)
       exec(@sql)  
    END  
END