Set @PageSize=1
print(@RecordCount)
if(@CurrentPage=1)
Begin
set Rowcount @PageSize
set @Sql=’select ‘+ @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’
‘+@OrderSql +’,’+@IdField +’ asc’
–print(@Sql)
exec sp_executeSql @Sql
End
else if(@CurrentPage=@TotalPage)
begin
set @afterRows=@RowsCount-(@CurrentPage-1)*@PageSize
set RowCount @afterRows
if(@OrderType=’1′)
begin
set @OrderField=REPLACE(@OrderField,’asc’,’lai512343975′)//这里用变量将asc和desc互换，哈哈，太神了
set @OrderField=REPLACE(@OrderField,’desc’,’asc’)
set @OrderField=REPLACE(@OrderField,’lai512343975′,’desc’)
set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’ desc’+’,’+@IdField +’ asc’
end
else
begin
set @OrderField=REPLACE(@OrderField,’desc’,’lai512343975′)
set @OrderField=REPLACE(@OrderField,’asc’,’desc’)
set @OrderField=REPLACE(@OrderField,’lai512343975′,’asc’)
set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’ asc ‘ +’,’+@IdField+ ‘ asc’
print(@Sql)
end
–print(@Sql)
exec sp_executeSql @Sql
end
else
Begin
set @nRd=@PageSize* (@CurrentPage-1)
print(@nRd)
set RowCount @PageSize
set @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ and ‘+@IdField + ‘ not in (select top ‘+ cast(@nRd as nvarchar(10))+’ ‘+@IdField+’ from ‘+@TableName+’ where ‘+ @strWhere+’ order by ‘+@OrderField +’ ‘+@OrderSql+’,’+@IdField +’ asc) ‘ + ‘ order by ‘+ @OrderField + ‘ ‘ +@OrderSql+’,’+@IdField +’ asc’
exec sp_executeSql @Sql
–Print(@sql)
End
end
GO
您可能感兴趣的文章:真正高效的SQLSERVER分页查询(多种方案)oracle,mysql,SqlServer三种数据库的分页查询的实例sqlserver分页的两种写法分别介绍sqlserver2005使用row_number() over分页的实现方法sqlserver 三种分页方式性能比较[图文]sqlserver 高性能分页实现分析sqlserver 千万数量级分页存储过程代码SQL Server 2012使用Offset/Fetch Next实现分页数据查询
