SQLServer 存储过程分页

USE [NetShop]
GO
/****** Object:  StoredProcedure [dbo].[proc_divPage]    Script Date: 10/26/2013 20:34:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_divPage]
@sqlStr nvarchar(1000),------查询语句
@pageSize int,------每页记录数
@pageIndex int,------页号（从1开始），第几页
@key varchar(50),----主键
@orderField varchar(100)                 ---排序字段
/*
SqlServer 通用存储过程
Top 分页
*/
as
--select*from item
declare @Sql varchar(3000)
set @sql = 'select top '+ cast(@pageSize as nvarchar)
+'* from ('+@sqlStr+') T where '
+@key+' not in (
select top ('+cast(@pageSize as nvarchar)
+'* ('+cast(@pageIndex as nvarchar)+'-1)) '+@key
+' from ('+@sqlStr +'  ) TX '
if(len(@orderField)>0)
  begin
    set @sql=@sql+' order by '+@orderField+')  order by '+@orderField
  end
else
   set @sql=@sql+')'
print @sql
exec(@sql)

public static void main(String[] args)throws Exception {
		Connection conn = getConnection();
		ResultSet rs = null;
		
		int pageSize = 15;
		int currentPage = 2;
		String sql = "select * from kq_tbl ";
		
		String st = "{call proc_divPage(?,?,?)}";
		
		CallableStatement cstmt = conn.prepareCall(st);
		
		cstmt.setString(1,sql);
		cstmt.setInt(2,pageSize);
		cstmt.setInt(3,currentPage);
		cstmt.setString(4,"id");
		cstmt.setString(5,"condition");
		
		if(cstmt.execute()){
			rs = cstmt.getResultSet();
			while(rs.next()){
				System.out.print(rs.getString("stuName"));
				System.out.println(rs.getInt("id"));
			}
		}
		
		
	}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!