if
exists
(
select
*
from
guest_table)
drop proc pagerepeat
go
create proc pageRepeat
@tblname varchar ( 255 ),
@fldname varchar ( 255 ),
@pageSize int ,
@pageIndex int ,
@pageCounts int output,
@totalRecord int output
as
declare @strSQL nvarchar ( 4000 ),
@totalCounts int ,
@wqe int ,
@sql NVARCHAR ( 400 ),
@sql1 NVARCHAR ( 400 )
set @sql = N ' select @totalCounts=count(*) from ' + @tblname
exec sp_executesql @sql ,N ' @totalcounts int output ' , @totalcounts output
set @pageCounts = ( @totalCounts / @pageSize )
set @totalRecord = @totalCounts
if ( @pageCounts + 1 ) >= @pageindex and @pageindex > 0
begin
if @pageIndex = 1
set @strSQL = ' select top ' + str ( @pageSize ) + ' * from ' + @tblname + ' order by ' + @fldname + ' desc '
else
set @strSQL = ' select top ' + str ( @pagesize ) + ' * from ' + @tblname + ' where ' + @fldname +
' <(select min( ' + @fldname + ' ) ' + @tblname + ' from (select top ' +
str (( @pageIndex - 1 ) * @pagesize ) + @fldname + ' from ' + @tblname + ' order by ' + @fldname + ' desc) as #temptab)order by ' + @fldname + ' desc '
exec sp_executesql @strSQL
end
else
begin
print ' 错误 '
end
go
declare @pagecounts int , @totalCounts int
exec pagerepeat '你的表名 ' , '排序字段 ' , 10 , 12 , @pagecounts output, @totalCounts output
select @pagecounts , @totalCounts
drop proc pagerepeat
go
create proc pageRepeat
@tblname varchar ( 255 ),
@fldname varchar ( 255 ),
@pageSize int ,
@pageIndex int ,
@pageCounts int output,
@totalRecord int output
as
declare @strSQL nvarchar ( 4000 ),
@totalCounts int ,
@wqe int ,
@sql NVARCHAR ( 400 ),
@sql1 NVARCHAR ( 400 )
set @sql = N ' select @totalCounts=count(*) from ' + @tblname
exec sp_executesql @sql ,N ' @totalcounts int output ' , @totalcounts output
set @pageCounts = ( @totalCounts / @pageSize )
set @totalRecord = @totalCounts
if ( @pageCounts + 1 ) >= @pageindex and @pageindex > 0
begin
if @pageIndex = 1
set @strSQL = ' select top ' + str ( @pageSize ) + ' * from ' + @tblname + ' order by ' + @fldname + ' desc '
else
set @strSQL = ' select top ' + str ( @pagesize ) + ' * from ' + @tblname + ' where ' + @fldname +
' <(select min( ' + @fldname + ' ) ' + @tblname + ' from (select top ' +
str (( @pageIndex - 1 ) * @pagesize ) + @fldname + ' from ' + @tblname + ' order by ' + @fldname + ' desc) as #temptab)order by ' + @fldname + ' desc '
exec sp_executesql @strSQL
end
else
begin
print ' 错误 '
end
go
declare @pagecounts int , @totalCounts int
exec pagerepeat '你的表名 ' , '排序字段 ' , 10 , 12 , @pagecounts output, @totalCounts output
select @pagecounts , @totalCounts