存储过程－－分页

 CREATE   PROCEDURE   sp_page
@CurrentPage   int,   @PageSize   int,@Field_Info   varchar(500),@Table_info   varchar(20),@Field_id   varchar(10),@intOrder   int,@otherwhere   varchar(50),@RecordCount   int   output,@PageCount   int   output
--@CurrentPage为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数
AS
DECLARE   @MinPage   int,   @MaxPage   int
declare   @sql   varchar(1000)
declare   @sqlt   nvarchar(300)
declare   @order   varchar(4)
set   @sqlt   =   'SELECT   @RecordCount   =   COUNT( '   +   @Field_id   +   ')   FROM   '   +   @Table_Info
exec   sp_executesql   @sqlt,N '@RecordCount   int   output ',@RecordCount   output           --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
IF   @PageSize   <=   0
begin
set   @PageSize   =   10
end
else   if   @PageSize   >   @RecordCount
begin
set   @pageSize   =   @RecordCount
end
set   @pagecount   =   @RecordCount   /   @PageSize
if   ((@recordcount   %   @pagesize)   !=   0) --如果除不尽则加一页
begin
set   @PageCount   =   @RecordCount   /   @PageSize
set   @PageCount   =   @pagecount   +   1
end
else
begin
set   @pagecount   =   @recordcount   /@PageSize
end
IF   @CurrentPage   <=   0
begin
set   @CurrentPage   =   1
end
else   if   @CurrentPage   >   @pagecount
begin
set   @currentpage   =   @pagecount --如果输入页数大于总页数则符最后一页
end
SET   @MinPage   =   (@CurrentPage   -   1)   *   @PageSize   +   1
SET   @MaxPage   =   @MinPage   +   @PageSize   -   1

BEGIN
if   @intorder   =   0
set   @order   =   'asc '
else
set   @order   =   'desc '
if   @Field_Info   like   ' '
set   @field_Info   =   '* '
if   @otherwhere   like   ' '
set   @sql   =   'select   '   +   @Field_Info   +   '   from   (select   '   +     @Field_Info   +   '   ,   row_number()   over(order   by   '   +   @Field_id   +   '   '   +   @Order   +   ')   as   rownumber   from   '   +   @Table_info   +   ')   as   a   where   rownumber   between   '   +   convert(varchar(10),@minpage)   +   '   and   '   +   convert(varchar(10),@maxpage)
else
set   @sql   =   'select   '   +   @Field_Info   +   '   from   (select   '   +     @Field_Info   +   '   ,   row_number()   over(order   by   '   +   @Field_id   +   '   '   +   @Order   +   ')   as   rownumber   from   '   +   @Table_info   +   ')   as   a   where   rownumber   between   '   +   convert(varchar(10),@minpage)   +   '   and   '   +   convert(varchar(10),@maxpage)   +   '   and   '   +   @otherwhere
exec(@sql)
END


declare   @rcon   int
declare   @pcon   int  
exec   sp_page   8,73, ' ', 'user_info ', 'id ',0, ' ',@rcon   output,@pcon   output


这是我修改别人的一个数据库分页的存储过程