mssql分页存储过程

/****** Object: Stored Procedure dbo.sp_pagination Script Date: 2006-1-11 10:28:02 ******/
-------------------------------------------------------------------------------------------------------------
----------分页
---------江达政
--------2005-9-30
----------------------------------------------------------------------------------------------------------
Create PROCEDURE sp_pagination
@sql   nvarchar(2000),   --SQL语句
@page   int,    --当前第几页
@pagesize  int,    --每页大小
@field   varchar(100),   --检索字段
@sortfield  varchar(100),   --排序字段
@totalrecord  int output,
@totalpage  int output


as

declare 
@str   nvarchar(3000),
@alls   int


------------------------------------------------------------------------------------------------
--计算总记录条数和页数
------------------------------------------------------------------------------------------------
set @str='select @all =count(*) from ('+ @sql+') t'
exec sp_executesql @str,N'@all int output',@totalrecord output

if @totalrecord % @pagesize=0
 set @totalpage=@totalrecord/@pagesize
else
 set @totalpage=@totalrecord/@pagesize+1
if @page<1
 set @page=1
if @page>=@totalpage
 set @page=@totalpage


--------------------------------------------------------------------------------------------------------
--返回记录集
--------------------------------------------------------------------------------------------------------
set @str='select top '+cast(@pagesize as varchar(10))+' * from ('+@sql+') t where '+@field+' not in (select top '+cast((@page-1)*@pagesize as varchar(10))+' '+@field+' from ('+@sql+') t9 order by '+@sortfield+') order by '+@sortfield
print @str
exec sp_executesql @str

GO