sql server 2008存储过程分页

USE [hospital]
GO
/****** Object:  StoredProcedure [dbo].[P_PAGE]    Script Date: 10/21/2012 10:33:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_PAGE]
	/**执行的sql语句*/
	@p_sql nvarchar(max),
	/**查询总数据条数的sql语句*/
	@p_sqlCount nvarchar(max),
	/**每页显示多少条数据*/
	@p_pageSizeStr nvarchar(max),
	/**当前页码*/
	@p_curentPageStr nvarchar(max) ,
	
	/**总数据条数*/
	@p_pageRows int output,
	/**当前页码*/
	@p_realCurentPage int output
	
	
as 
begin
	declare @p_pageSize int,@p_curentPage int;
	set @p_pageSize = CONVERT(int, @p_pageSizeStr);
	set @p_curentPage = CONVERT(int, @p_curentPageStr);
	/*总数据条数*/
	declare @m_pageRows int;	
	/*总页数*/
	declare @m_pageCount int;	
	/**首先查询总页数**/
	declare @count_string nvarchar(max);
	set @count_string = 'select @pageRows=count(*) from ('+@p_sqlCount+') pcount';
	exec  sp_executesql  @count_string,N'@pageRows int output',@m_pageRows   output;
	set @p_pageRows = @m_pageRows;
	
	/**根据总记录跳数计算出总页数**/
	set @m_pageCount = CEILING((@m_pageRows+0.0)/@p_pageSize);
	
	if @p_curentPage <= 1
		begin
			set @p_realCurentPage = 1;
		end
	
	else if (@p_curentPage > @m_pageCount)
		begin
			set @p_realCurentPage = @m_pageCount;
		end
	
	else
		begin
			set @p_realCurentPage = @p_curentPage;
		end
	
	declare @sql nvarchar(max);
	declare @startIndex int;

	set @startIndex =(@p_realCurentPage-1)*@p_pageSize;
	
	/**
	set @sql = 'select * from ('+@p_sql+') as tb where rownumber between '+CONVERT(nvarchar(max),@startIndex)+' and '+@p_pageSizeStr;
	**/
	set @sql = 'select top '+@p_pageSizeStr+' * from ('+@p_sql+') as tb where rownumber > '+CONVERT(nvarchar(max),@startIndex);
	exec  sp_executesql @sql output;
	
end



/**存储过程调用*/
declare @sql nvarchar(max), @sqlcount nvarchar(max);
set @sql = 'select row_number() over(order by createTime desc ) as rownumber, id as id, code as code, name as name, createTime as createTime from sys_notice where (1=1) and (1=1) and (1=1) and (1=1) ';
set @sqlcount = 'select id as id from sys_notice where (1=1) and (1=1) and (1=1) and (1=1) '
declare @i int,@j int;
exec P_PAGE @sql,@sqlcount,'10','2',@i,@j;

/**java的调用*/

/**
	 * 根据查询语句，当前页码，每页显示的数量查找数据
	 * @param sqlStr ：查询语句 : 在查询语句传进来前请先格式化SqlStr
	 * @param curPage ：当前页码
	 * @param pageSize : 每页显示的数量
	 */
	public void execute(final String sqlStr, final String sqlCount, final int curPage, final int pageSize, PageUtils pageUtils) {
		long a1 = System.currentTimeMillis();
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		ResultSetMetaData meta = null;
		//总记录数
		int rowsCount = 0;
		//当前页
		int p_curPage = 1;
		//总页数
		int p_pageCount = 0;
		try {
			conn = DataSourceUtils.getConnection(getSqlMapClient().getDataSource());
			String sql = "{call p_page(?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1, sqlStr);
			cs.setString(2, sqlCount);
			cs.setString(3, pageSize+"");
			cs.setString(4, curPage+"");
			cs.registerOutParameter(5, Types.INTEGER);
			cs.registerOutParameter(6, Types.INTEGER);
			rs = cs.executeQuery();
			
				rs = cs.getResultSet();
				long a2 = System.currentTimeMillis();
				List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
				Map<String, Object> map = null;
				meta = rs.getMetaData();
				int columnCount = meta.getColumnCount();
				Object value = null;
				while (rs.next()) {
					map = new HashMap<String, Object>();
					for (int i = 0; i < columnCount; i++) {
						
						value = rs.getObject(i + 1);
						if(null == value){
							map.put(rs.getMetaData().getColumnName(i + 1), "");
						}else{
							if("DATETIME".equals(rs.getMetaData().getColumnTypeName(i+1))){
								map.put(rs.getMetaData().getColumnName(i + 1), DateUtils.formatDateToString(rs.getTimestamp(i + 1),"yyyy-MM-dd HH:mm:ss"));
							}else{
								map.put(rs.getMetaData().getColumnName(i + 1), value.toString());
							}
						}
					}
					list.add(map);
				}
				if (!cs.getMoreResults()) {
					rowsCount = cs.getInt(5);
					p_curPage = cs.getInt(6);
				}
				p_pageCount = (int) Math.ceil((double) rowsCount
	                    / pageSize);//页数
				pageUtils.setCurPage(p_curPage);
				pageUtils.setPageCount(p_pageCount);
				pageUtils.setRowsCount(rowsCount);
				pageUtils.setList(list);
				
//				page.setCurPage(p_curPage);
//				page.setPageSize(p_pageSize);
//				page.setPageCount(rowsCount);
//				page.setDataList(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtils.releaseConnection(conn, getSqlMapClient().getDataSource());
		}
	}