if exists (select * from sysobjects where name='[GetDataList]')
    drop proc [GetDataList]
go
CREATE PROCEDURE [dbo].[GetDataList]
    @TableName varchar(500)='', --表名(多表连接table1,table2然后用where字段处理或者table1 inner join table2 on col1=col2)
    @Fields varchar(5000) = '*', --字段名(全部字段为*)
    @OrderField varchar(5000), --排序字段(必须!支持多字段)
    @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
    @pageSize int, --每页多少条记录
    @pageIndex int = 1 , --指定当前为第几页
    @groupby int=0,
    @TotalPage int output, --返回总页数 
    @totalRecord int output 
as
    begin
    --Begin Tran --开始事务
        Declare @sql nvarchar(4000)
        --计算总记录数

        if (@SqlWhere='' or @sqlWhere=NULL)
            begin
            if(@groupby=1)
                    begin
                        set @Sql = 'select @totalRecord = count(*) from (Select count(*) as ct from ' + @TableName+' group by '+@Fields+') as t'
                    end
            else
                set @sql = 'select @totalRecord = count(*) from ' + @TableName
                
            end
        else
            begin
            if(@groupby=1)
                    begin
                        set @Sql =  'select @totalRecord = count(*) from (select count(*) as ct from ' + @TableName + ' where ' + @sqlWhere+' group by '+@Fields+') as t'
                    end
            else
                set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
                
            end
            print @sql
        EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 

            --计算总页数
            select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
        if (@SqlWhere='' or @sqlWhere=NULL)
            begin
                if(@OrderField='' or @OrderField= NULL)
                    begin
                        set @sql = 'Select '+@Fields+' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
                            if(@groupby=1)
                                begin
                                    set @Sql = @Sql+' group by '+@Fields
                                end
                    end
                else
                    begin
                        set @sql = 'Select '+@Fields+' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
                            if(@groupby=1)
                                begin
                                    set @Sql = @Sql+' group by '+@Fields
                                end
                    end
            end
        else
        begin
            if(@OrderField='' or @OrderField= NULL)
                begin
                    set @sql = 'Select '+@Fields+' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere 
                    if(@groupby=1)
                        begin
                            set @Sql = @Sql+' group by '+@Fields
                        end
                end
            else
                begin
                    set @sql = 'Select  '+@Fields+' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere 
                    if(@groupby=1)
                        begin
                            set @Sql = @Sql+' group by '+@Fields
                        end
                end
        end
        --处理页数超出范围情况
        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 ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
        
        if(@groupby=1)
            begin
                set @Sql = @Sql+' group by '+@Fields
            end
            if(@OrderField<>'')
                begin
                    set @Sql =  @Sql +' order by '+@OrderField
                end 
        --print(@Sql)
        Exec(@Sql)
        -----------------------------------------------------
        --If @@Error <> 0
        --    Begin
        --        RollBack Tran
        --        Return -1
        --    End
        --else
        --    Begin
        --        Commit Tran
        --        print(@totalRecord)
        --        Return @totalRecord ---返回记录总数
        --    End 
    end
GO