存储过程:

ALTER PROCEDURE spPagination

    @FieldList Nvarchar(200),--字段列表
    @TableName Nvarchar(20), --表名
    @WhereStr Nvarchar(500),--条件语句(须写完整,如"where Name='sea' and image=0",如果使用OR语句,须用():如:"Where (Name='sea' OR image=0)"
    @PrimaryKey Nvarchar(20),--主键
    @SortStr Nvarchar(100),--排序语句(须写完整,如"Order By ID,Nname")
    @SortStrDesc Nvarchar(100), --倒序语句(须写完整,如"Order By ID desc,Nname desc")
    @PageSize int,--页记录数
    @PageNo int,--页码
    @RecordCount int OUTPUT,--返回记录总数    
    @PageCount int OUTPUT--返回页总数    


AS
    /*定义局部变量*/
    declare @intBeginID         nvarchar(20)
    declare @intEndID           nvarchar(20)
    declare @intRecordCount     int
    declare @intRowCount        int
    declare @TmpSelect          NVarchar(600)
    /*关闭计数*/
    set nocount on
   
   /*
   set @PageNo=7
   set @PageSize=2
   set @SortStr='order by subproclassid, ProductID'
   set @SortStrDesc='order by subproclassid desc, ProductID desc'
   */
    /*求总记录数*/
    Set @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@TableName+' '+@WhereStr
    execute sp_executesql
            @TmpSelect,
            N'@SPintRootRecordCount int OUTPUT',
            @SPintRootRecordCount=@intRecordCount OUTPUT
             
    /*返回总记录数*/             
    set @RecordCount = @intRecordCount
    
    if @intRecordCount=0
        --没有记录则返回一个空记录集
        Begin
            Set @TmpSelect='Select ' + @FieldList + ' from '+@TableName+' '+@WhereStr    
            Execute sp_executesql @TmpSelect
            set @RecordCount=0
            set @PageCount=1
        End
    else
        --有记录则返回记录集
        begin    
    /*返回总页数*/
    if @intRecordCount <> 0
        begin
            set @PageCount=floor((@intRecordCount+1.0-1.0) / @PageSize)
            if @PageCount<(@intRecordCount+1.0-1.0) / @PageSize