获取分页的每页结果存储过程

CREATEPROCEDURE[dbo].[mst_sp_pageshowex4]--输入参数@qColsvarchar(8000),--@qTablesvarchar(8000),--@qWherevarchar(8000),--@oKeyvarchar(100),--@pageSizeint,--@pageNumberint--,0AS--不生成影响行数setnocountonBEGIN--声明变量DECLARE@sqlstrASvarchar(max)DECLARE@sqlTableASvarchar(8000)set@sqltable='SELECTROW_NUMBER()OVER(ORDERBY'+@oKey+')ASRowId,'+@qCols+'FROM'+@qTables+'where'+@qWhere;set@sqlstr='SELECT*FROM('+@sqlTable+')ASDWHERERowIdbetween'+str(@pageNumber*@pagesize+1)+'AND'+str((@pageNumber+1)*@pagesize);exec(@sqlstr);END


获取分页总记录数目存储过程

createPROCEDURE[dbo].[sp_pagecount]@qfromTablesvarchar(8000),--来自哪几张表@qwherevarchar(8000)ASBEGINDECLARE@sqlstrASvarchar(8000)set@sqlstr='SELECTCOUNT(*)FROM'+@qfromTables+'where'+@qwhere;print@sqlstr;exec(@sqlstr);END



sql映射文件Article.xml配置

<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEsqlMapPUBLIC"-//ibatis.apache.org//DTDSQLMap2.0//EN""http://ibatis.apache.org/dtd/sql-map-2.dtd"><sqlMapnamespace="Article"><selectid="queryForPaginate2"parameterClass="article"resultClass="article">mst_sp_pageshowex4'$columns$','$table$','$where$','$orderBy$',$pageSize$,$pageNo$</select><selectid="count2"parameterClass="article"resultClass="integer">sp_pagecount'$table$','$where$'</select></sqlMap>