SQL分页存储过程.docx

1、SQL分页存储过程-参数说明-获取指定页的数据CREATEPROCEDUREpaginationtblNamevarchar(255),-表名strGetFieldsvarchar(1000)=*,-需要返回的列fldNamevarchar(255)=,-排序的字段名PageSizeint=10,-页尺寸PageIndexint=1,-页码doCountbit=0,-返回记录总数,非0值则返回OrderTypebit=0,-设置排序类型,非0值则降序strWherevarchar(1500)=-查询条件(注意:不要加where)ASdeclarestrSQLvarchar(5000)-主语句d

2、eclarestrTmpvarchar(110)-临时变量declarestrOrdervarchar(400)-排序类型ifdoCount!=0beginifstrWhere!=setstrSQL=selectcount(*)asTotalfrom+tblName+where+strWhereelsesetstrSQL=selectcount(*)asTotalfrom+tblName+end-以上代码的意思是如果doCount传递过来的不是0，就执行总数统计。以下的所有代码都是doCount为0的情况elsebeginifOrderType!=0beginsetstrTmp=(select

3、maxsetstrOrder=orderby+fldName+ascendifPageIndex=1beginifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+strWhere+strOrderelsesetstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+strOrder-如果是第一页就执行以上代码，这样会加快执行速度endelsebegin-以下代码赋予了strSQL以真正执行的SQL代码setstrSQL=selectt

4、op+str(PageSize)+strGetFields+from+tblName+where+fldName+strTmp+(+fldName+)from(selecttop+str(PageIndex-1)*PageSize)+fldName+from+tblName+strOrder+)astblTmp)+strOrderifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+fldName+strTmp+(+fldName+)from(selecttop+str(PageIndex-1

5、)*PageSize)+fldName+from+tblName+where+strWhere+strOrder+)astblTmp)and+strWhere+strOrderendendexec(strSQL)GO于是我对效率最高的selectmax方法用2分法进行了扩展，代码取自互联网，我修改了ASC排序时取不到值的BUG，测试结果: 存过第2页耗时第1000页第10000页第100000页第199999页效率排行2分法156ms156ms180ms470ms156ms1*从测试结果来看，使用2分法确实可以提高效率并使效率更为稳定，我又增加了第159999页的测试，用时仅296ms，效果相

6、当的不错！ 下面是2分法使用selectmax的代码，已相当完善。 -/*-存储过程 分页处理 孙伟 2005-03-28创建 -*/-/*-存储过程 分页处理 浪尘 2008-9-1修改-*/-/*- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -*/alter PROCEDURE proc_paged_2part_selectMax(tblName nvarchar(200), -要显示的表或多个表的连接fldName nvarchar(500) = *, -要显示的字段列表pageSize int = 10, -每页显示的记录个数page int = 1, -要显示

7、那一页的记录fldSort nvarchar(200) = null, -排序字段列表或条件Sort bit = 0, -排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)-程序传参如： SortA Asc,SortB Desc,SortC )strCondition nvarchar(1000) = null, -查询条件,不需whereID nvarchar(150), -主表的主键Dist bit = 0, -是否添加查询字段的 DISTINCT 默认0不添加/1添加pageCount int = 1 output, -查

8、询结果分页后的总页数Counts int = 1 output -查询到的记录数)ASSET NOCOUNT ONDeclare sqlTmp nvarchar(1000) -存放动态生成的SQL语句Declare strTmp nvarchar(1000) -存放取得查询结果总数的查询语句Declare strID nvarchar(1000) -存放取得查询开头或结尾ID的查询语句Declare strSortType nvarchar(10) -数据排序规则ADeclare strFSortType nvarchar(10) -数据排序规则BDeclare SqlSelect nvarc

9、har(50) -对含有DISTINCT的查询进行SQL构造Declare SqlCounts nvarchar(50) -对含有DISTINCT的总数查询进行SQL构造declare timediff datetime -耗时测试时间差select timediff=getdate()if Dist = 0beginset SqlSelect = select set SqlCounts = Count(*)endelsebeginset SqlSelect = select distinct set SqlCounts = Count(DISTINCT +ID+)endif Sort=0b

10、eginset strFSortType= ASC set strSortType= DESC endelsebeginset strFSortType= DESC set strSortType= ASC end-生成查询语句-此处strTmp为取得查询结果数量的语句if strCondition is null or strCondition= -没有设置显示条件beginset sqlTmp = fldName + From + tblNameset strTmp = SqlSelect+ Counts=+SqlCounts+ FROM +tblNameset strID = From

11、+ tblNameendelsebeginset sqlTmp = + fldName + From + tblName + where (10) + strConditionset strTmp = SqlSelect+ Counts=+SqlCounts+ FROM +tblName + where (10) + strConditionset strID = From + tblName + where (10) + strConditionend-取得查询结果总数量-exec sp_executesql strTmp,NCounts int out ,Counts outdeclare

12、 tmpCounts intif Counts = 0set tmpCounts = 1elseset tmpCounts = Counts-取得分页总数 set pageCount=(tmpCounts+pageSize-1)/pageSize/*当前页大于总页数 取最后一页*/if pagepageCountset page=pageCount-/*-数据分页2分处理-*/ declare pageIndex int -总数/页大小 declare lastcount int -总数%页大小set pageIndex = tmpCounts/pageSizeset lastcount =

13、tmpCounts%pageSizeif lastcount 0set pageIndex = pageIndex + 1elseset lastcount = pagesize-/*显示分页 if strCondition is null or strCondition= -没有设置显示条件 beginif pageIndex2 or page=pageIndex / 2 + pageIndex % 2 -前半部分数据处理 beginif page=1set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ fro

14、m +tblName+ order by + fldSort + + strFSortTypeelsebeginif Sort=1beginset strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName+ where +ID+ (select max(+ ID +) from (+ SqlSelect+ top + CAST(pageSize*(page-1) as Varchar(20) + + ID + from +tblName+ order by + fldSort + + strFSo

15、rtType+) AS TBMinID)+ order by + fldSort + + strFSortTypeendendendelsebeginset page = pageIndex-page+1 -后半部分数据处理 if page (select max(+ ID +) from(+ SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName+ order by + fldSort + + strSortType+) AS TBMaxID)+ order by + fl

16、dSort + + strSortType+) AS TempTB+ order by + fldSort + + strFSortTypeendelsebeginset strTmp=SqlSelect+ * from (+SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName+ where +ID+ (select min(+ ID +) from(+ SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + fro

17、m +tblName+ order by + fldSort + + strSortType+) AS TBMaxID)+ order by + fldSort + + strSortType+) AS TempTB+ order by + fldSort + + strFSortTypeendendendelse -有查询条件 beginif pageIndex2 or page=pageIndex / 2 + pageIndex % 2 -前半部分数据处理 beginif page=1set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(

18、4)+ + fldName+ from +tblName+ where 1=1 + strCondition + order by + fldSort + + strFSortTypeelse if(Sort=1)beginset strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName+ where +ID+ (select max(+ ID +) from (+ SqlSelect+ top + CAST(pageSize*(page-1) as Varchar(20) + + ID + fr

19、om +tblName+ where (1=1) + strCondition + order by + fldSort + + strFSortType+) AS TBMinID)+ + strCondition + order by + fldSort + + strFSortTypeendendelsebeginset page = pageIndex-page+1 -后半部分数据处理 if page (select max(+ ID +) from(+ SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) +

20、+ ID + from +tblName+ where (1=1) + strCondition + order by + fldSort + + strSortType+) AS TBMaxID)+ + strCondition+ order by + fldSort + + strSortType+) AS TempTB+ order by + fldSort + + strFSortTypeelseset strTmp=SqlSelect+ * from (+SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tb

21、lName+ where +ID+ (select min(+ ID +) from(+ SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName+ where (1=1) + strCondition + order by + fldSort + + strSortType+) AS TBMaxID)+ + strCondition+ order by + fldSort + + strSortType+) AS TempTB+ order by + fldSort + + strFSortTypeendend-返回查询结果-exec sp_executesql strTmpselect datediff(ms,timediff,getdate() as 耗时-print strTmpSET NOCOUNT OFFGO执行示例： exec proc_paged_2part_selectMax tb_testTable,ID,userName,userPWD,