第一种：单表多字段排序分页存储过程

--支持单表多字段查询，多字段排序create PROCEDURE [dbo].[UP_GetByPageFiledOrder]( @TableName varchar(50),            --表名 @ReFieldsStr varchar(200) = ‘*‘,   --字段名(全部字段为*) @OrderString varchar(200),         --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N‘‘,  --条件语句(不用加where) @PageSize int,                     --每页多少条记录 @PageIndex int = 1 ,               --指定当前为第几页 @TotalRecord int output            --返回总记录数)AS  BEGIN         --处理开始点和结束点    Declare @StartRecord int;    Declare @EndRecord int;     Declare @TotalCountSql nvarchar(500);     Declare @SqlString nvarchar(2000);        set @StartRecord = (@PageIndex-1)*@PageSize + 1    set @EndRecord = @StartRecord + @PageSize - 1     SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--总记录数语句    SET @SqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;--查询语句    --    IF (@WhereString! = ‘‘ or @WhereString!=null)        BEGIN            SET @TotalCountSql=@TotalCountSql + ‘  where ‘+ @WhereString;            SET @SqlString =@SqlString+ ‘  where ‘+ @WhereString;                    END    --第一次执行得到    --IF(@TotalRecord is null)    --   BEGIN           EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--返回总记录数    --  END    ----执行主语句    set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ +  ltrim(str(@EndRecord));    Exec(@SqlString)    END

下面是调用存储过程--exec [PROCE_SQL2005PAGECHANGE] ‘T_ScInfo‘,‘*‘,‘by2 desc,addtime desc‘,‘ by1=4 and state = 1 ‘,‘2‘,‘3‘,‘‘

第二种：单表效率最高的分页存储过程

动软分页存储过程

ALTER PROCEDURE [dbo].[UP_GetRecordByPage]    @tblName      varchar(255),       -- 表名    @fldName      varchar(255),       -- 主键字段名    @PageSize     int = 10,           -- 页尺寸    @PageIndex    int = 1,            -- 页码    @IsReCount    bit = 0,            -- 返回记录总数, 非0 值则返回    @OrderType    bit = 0,            -- 设置排序类型, 非0 值则降序    @strWhere     varchar(1000) = ‘‘ -- 查询条件(注意: 不要加where)ASdeclare @strSQL   varchar(6000)       -- 主语句declare @strTmp   varchar(1000)        -- 临时变量(查询条件过长时可能会出错，可修改为)declare @strOrder varchar(400)        -- 排序类型if @OrderType != 0begin    set @strTmp = ‘<(select min‘    set @strOrder = ‘ order by [‘ + @fldName +‘] desc‘endelsebegin    set @strTmp = ‘>(select max‘    set @strOrder = ‘ order by [‘ + @fldName +‘] asc‘endset @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘    + @tblName + ‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘    + @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘    + @fldName + ‘] from [‘ + @tblName + ‘]‘ + @strOrder + ‘) as tblTmp)‘    + @strOrderif @strWhere != ‘‘    set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘        + @tblName + ‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘        + @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘        + @fldName + ‘] from [‘ + @tblName + ‘] where ‘ + @strWhere + ‘ ‘        + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrderif @PageIndex = 1begin    set @strTmp =‘‘    if @strWhere != ‘‘        set @strTmp = ‘ where ‘ + @strWhere    set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘        + @tblName + ‘]‘ + @strTmp + ‘ ‘ + @strOrderendif @IsReCount != 0    set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘+‘ where ‘ + @strWhereexec (@strSQL)