获取分页的每页结果存储过程
CREATE PROCEDURE [dbo].[mst_sp_pageshowex4]
--输入参数
@qCols varchar(8000), --
@qTables varchar(8000), --
@qWhere varchar(8000), --
@oKey varchar(100), --
@pageSize int, --
@pageNumber int --, 0
AS
--不生成影响行数
set nocount on
BEGIN
--声明变量
DECLARE @sqlstr AS varchar(max)
DECLARE @sqlTable AS varchar(8000)
set @sqltable = 'SELECT ROW_NUMBER() OVER(ORDER BY '+ @oKey + ' ) AS RowId,' + @qCols + ' FROM '+@qTables + ' where ' + @qWhere ;
set @sqlstr = 'SELECT * FROM ( '+@sqlTable+' ) AS D WHERE RowId between ' + str(@pageNumber*@pagesize +1) + ' AND ' + str((@pageNumber +1)*@pagesize) ;
exec (@sqlstr);
END
获取分页总记录数目存储过程
create PROCEDURE [dbo].[sp_pagecount]
@qfromTables varchar(8000), -- 来自哪几张表
@qwhere varchar(8000)
AS
BEGIN
DECLARE @sqlstr AS varchar(8000)
set @sqlstr='SELECT COUNT(*) FROM ' + @qfromTables +' where ' +@qwhere;
print @sqlstr;
exec (@sqlstr);
END
sql映射文件Article.xml配置
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="Article"> <select id="queryForPaginate2" parameterClass="article" resultClass="article"> mst_sp_pageshowex4 '$columns$ ','$table$','$where$','$orderBy$',$pageSize$,$pageNo$ </select> <select id="count2" parameterClass="article" resultClass="integer"> sp_pagecount '$table$','$where$' </select> </sqlMap>
本文出自 “点滴积累” 博客,请务必保留此出处http://tianxingzhe.blog.51cto.com/3390077/1720357