存储过程分页以及实现

publicDataSetRunProcedure(stringstoredProcName,IDataParameter[]parameters,stringtableName){using(SqlConnectionconnection=newSqlConnection(connectionString)){DataSetdataSet=newDataSet();connection.Open();SqlDataAdaptersqlDA=newSqlDataAdapter();SqlCommandcommand=newSqlCommand(storedProcName,connection);command.CommandType=CommandType.StoredProcedure;foreach(SqlParameterparameterinparameters){command.Parameters.Add(parameter);}sqlDA.SelectCommand=command;sqlDA.Fill(dataSet,tableName);connection.Close();returndataSet;}}}}一个高效分页存储过程(使用二分法,测试数据为200W以上)--建表createtabletb_TestTable(idintidentity(1,1)primarykeynotnull,userNamenvarchar(50)null,userPwdnvarchar(50)null,useremailnvarchar(200)null)--插入数据declare@countintset@count=1while@count=10000000begininsertintotb_TestTable(userName,userPwd,useremail)values('admin','admin888','[e-mail]')set@count=@count+1endifexists(select*fromdbo.sysobjectswherename='proc_paged_2part_selectMax')dropprocedureproc_paged_2part_selectMaxgocreateprocproc_paged_2part_selectMax(@tblNamenvarchar(200),----要显示的表或多个表的连接@fldNamenvarchar(500)='*',----要显示的字段列表@pageSizeint=10,----每页显示的记录个数@pageint=1,----要显示那一页的记录@fldSortnvarchar(200)=null,----排序字段列表或条件/**排序方法，0为升序，1为降序*(如果是多字段排列Sort指代最后一个排序字段的排列顺序*(最后一个排序字段不加排序标记)--程序传参如：'SortAAsc,SortBDesc,SortC')**/@Sortbit=0,@strConditionnvarchar(1000)=null,----查询条件,不需where@IDnvarchar(150),----主表的主键@Distbit=0,----是否添加查询字段的DISTINCT@pageCountint=1output,----查询结果分页后的总页数@Countsint=1output----查询到的记录数)assetnocountondeclare@sqlTmpnvarchar(1000)----存放动态生成的SQL语句declare@strTmpnvarchar(1000)----存放取得查询结果总数的查询语句declare@strIDnvarchar(1000)----存放取得查询开头或结尾ID的查询语句declare@strSortTypenvarchar(10)----数据排序规则Adeclare@strFSortTypenvarchar(10)----数据排序规则Bdeclare@SqlSelectnvarchar(50)----对含有DISTINCT的查询进行SQL构造declare@SqlCountsnvarchar(50)----对含有DISTINCT的总数查询进行SQL构造declare@timediffdatetime--耗时测试时间差select@timediff=getdate()if@Dist=0beginset@SqlSelect='select'set@SqlCounts='Count(*)'endelsebeginset@SqlSelect='selectdistinct'set@SqlCounts='Count(DISTINCT'+@ID+')'endif@Sort=0beginset@strFSortType='ASC'set@strSortType='DESC'endelsebeginset@strFSortType='DESC'set@strSortType='ASC'end--------生成查询语句----------此处@strTmp为取得查询结果数量的语句if@strConditionisnullor@strCondition=''--没有设置显示条件beginset@sqlTmp=@fldName+'From'+@tblNameset@strTmp=@SqlSelect+'@Counts='+@SqlCounts+'FROM'+@tblNameset@strID='From'+@tblNameendelsebeginset@sqlTmp=+@fldName+'From'+@tblName+'where(10)'+@strConditionset@strTmp=@SqlSelect+'@Counts='+@SqlCounts+'FROM'+@tblName+'where(10)'+@strConditionset@strID='From'+@tblName+'where(10)'+@strConditionend----取得查询结果总数量-----execsp_executesql@strTmp,N'@Countsintout',@Countsoutdeclare@tmpCountsintif@Counts=0set@tmpCounts=1elseset@tmpCounts=@Counts--取得分页总数set@pageCount=(@tmpCounts+@pageSize-1)/@pageSize/**//**//**//**当前页大于总页数取最后一页**/if@page@pageCountset@page=@pageCount/*-----数据分页2分处理-------*/declare@pageIndexint--总数/页大小declare@lastcountint--总数%页大小set@pageIndex=@tmpCounts/@pageSizeset@lastcount=@tmpCounts%@pageSizeif@lastcount0set@pageIndex=@pageIndex+1elseset@lastcount=@pagesize--//***显示分页if@strConditionisnullor@strCondition=''--没有设置显示条件beginif@pageIndex2or@page=@pageIndex/2+@pageIndex%2--前半部分数据处理beginif@page=1set@strTmp=@SqlSelect+'top'+CAST(@pageSizeasVARCHAR(4))+''+@fldName+'from'+@tblName+'orderby'+@fldSort+''+@strFSortTypeelsebeginif@Sort=1beginset@strTmp=@SqlSelect+'top'+CAST(@pageSizeasVARCHAR(4))+''+@fldName+'from'+@tblName+'where'+@ID+'(selectmin('+@ID+')from('+@SqlSelect+'top'+CAST(@pageSize*(@page-1)asVarchar(20))+''+@ID+'from'+@tblName+'orderby'+@fldSort+''+@strFSortType+')ASTBMinID)'+'orderby'+@fldSort+''+@strFSortTypeendelsebeginset@strTmp=@SqlSelect+'top'+CAST(@pageSizeasVARCHAR(4))+''+@fldName+'from'+@tblName+'where'+@ID+'(selectmax('+@ID+')from('+@SqlSelect+'top'+CAST(@pageSize*(@page-1)asVarchar(20))+''+@ID+'from'+@tblName+'orderby'+@fldSort+''+@strFSortType+')ASTBMinID)'+'orderby'+@fldSort+''+@strFSortTypeendendendelsebeginset@page=@pageIndex-@page+1--后半部分数据处理if@page=1--最后一页数据显示set@strTmp=@SqlSelect+'*from('+@SqlSelect+'top'+CAST(@lastcountasVARCHAR(4))+''+@fldName+'from'+@tblName+'orderby'+@fldSort+''+@strSortType+')ASTempTB'+'orderby'+@fldSort+''+@strFSortTypeelseif@Sort=1beginset@strTmp=@SqlSelect+'*from('+@SqlSelect+'top'+CAST(@pageSizeasVARCHAR(4))+''+@fldName+'from'+@tblName+'where'+@ID+'(selectmax('+@ID+')from('+@SqlSelect+'top'+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))+''+@ID+'from'+@tblName+'orderby'+@fldSort+''+@strSortType+')ASTBMaxID)'+'orderby'+@fldSort+''+@strSortType+')ASTempTB'+'orderby'+@fldSort+''+@strFSortTypeendelsebeginset@strTmp=@SqlSelect+'*from('+@SqlSelect+'top'+CAST(@pageSizeasVARCHAR(4))+''+@fldName+'from'+@tblName+'where'+@ID+'(selectmin('+@ID+')from('+@SqlSelect+'top'+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))+''+@ID+'from'+@tblName+'orderby'+@fldSort+''+@strSortType+')ASTBMaxID)'+'orderby'+@fldSort+''+@strSortType+')ASTempTB'+'orderby'+@fldSort+''+@strFSortTypeendendendelse--有查询条件beginif@pageIndex2or@page=@page