存储过程分页问题

Question

CREATE proc sp_DataPage
(
@RecordCount int output,
@QueryStr nvarchar(100)='table1',--表名、视图名、查询语句
@PageSize int=20,  --每页的大小(行数)
@PageCurrent int=1,  --要显示的页
@FdShow nvarchar (1000)='*', --要显示的字段列表
@IdentityStr nvarchar (100)='id', --主键
@WhereStr nvarchar (200)='1=1 and id % 11111 = 0',
@FdOrder nvarchar(100)='id', --排序
@isReturn bit=0
)
as
declare @sql nvarchar(2000)
declare @rs varchar(50)
declare @tsql nvarchar(200),@WStr nvarchar (200)
set @sql = ''
set @rs = ''
if(@isReturn=1)
begin
 set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr +' '+ @WhereStr
 exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
end
else 
begin
 set @RecordCount = @PageSize * @PageCurrent + 1
end
if @PageCurrent = 1 
begin
 set @sql = 'select top  ' +  cast(@PageSize as varchar(3)) + ' "  ' +rtrim(cast ( @RecordCount as varchar(4)) )+' " as dzhrowcount,'+ @FdShow + ' from ' + @QueryStr + '  ' + @WhereStr + ' order by ' +@FdOrder 
end
else 
begin
set @sql = ' SELECT '+' "' +rtrim(cast ( @RecordCount as varchar(4)) )+' " as dzhrowcount , * from ( select top '+  cast(@PageSize*(@PageCurrent) as nvarchar(10)) +' '+@FdShow +'  from '+ @QueryStr + '  ' + @WhereStr + ' order by ' +@FdOrder  +' )  a where a.'+@IdentityStr ++' not in (select top '+cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + '  id  from ' + @QueryStr  + '  ' + @WhereStr + ' order by ' +@FdOrder  +')'
  
-- set @sql = 'select top '  +    cast(@PageSize as nvarchar(3)) + ' "  ' +rtrim(cast ( @RecordCount as char(4)) )+' " as dzhrowcoun,'+ @FdShow + ' from ' + @QueryStr + '  ' + @wstr + '  ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + '  ' + @WhereStr + ' order by ' + @FdOrder  + ') as t) order by ' + @FdOrder 
end
print @sql
execute(@sql)
return @RecordCount
GO

Answer 1

可以换个思路实现，1.先来个变量表 declare @tablename table   把行填充到这个表里
2.来个输出参数，把这个表的总行数返回。
3.在中间曾使用NameValueCollection 重构查询字符串。我在网上看到的代码，思路很清晰，最大的好处是，每次sql只传递给你当前页的数据，而不是把所有的数据都传给你

Answer 2

适合于任何表
Create PROCEDURE  PageProc_GetRecordByPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0,            -- 是否返回记录总数, 非 0 值则返回
    @IsRePageCount  bit=0,            -- 是否返回页面总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(100)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName +'] desc  '
end
else
begin
    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 = 1
begin
    set @strTmp =''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsRePageCount=1
begin 
    declare @Der varchar(1000)
    set @Der = ' declare @Count as int 
 set @Count = (select count(*) from ' + @tblName + ' where ' +  @strWhere  + ') '
    set @Der = @Der + ' declare @PageCount decimal(18,2) '+' set @PageCount = cast(convert(decimal(18,2),@Count)/convert(decimal(18,2),' + convert(nvarchar(10),@PageSize) + ') as decimal(18,2))   '
    set @Der = @Der + ' declare @PageFloor int ' + ' set @PageFloor=floor(@PageCount) ' + ' if cast(@PageFloor as decimal(18,2)) < @PageCount   select @PageFloor+1 as PageCount  else  select @PageFloor as PageCount '
 set @strSQL = @strSQL + @Der
endif @IsReCount != 0
    set @strSQL = @strSQL + ' select count(*) as Total from [' + @tblName + ']'+' where  ' + @strWhereexec (@strSQL)