问题描述
支持分页和排序功能，传入SQL语句、页索引、页容量即可实现分页的存储过程，急用！
解决方案
解决方案二：
网上搜一下吧，这个存储过程一大把，
解决方案三：
setANSI_NULLSONsetQUOTED_IDENTIFIERONgoCREATEprocedure[dbo].[sp_jinqi_new_article_dal]@sqlWherevarchar(5000)='',--条件语句(不用加where)@pageSizeint=10,--每页多少条记录@pageIndexint=1,--指定当前为第几页@totalRecordint=0output--返回记录总数AS/*用于分页查询记录*/BEGINSETNOCOUNTON;Declare@sqlnvarchar(4000);Declare@totalPageint;--计算总记录数set@sql='select@totalRecord=count(*)fromTable1tinnerjoinTable2son(t.column_id=s.column_idandt.language_id=s.language_id)wheret.flag=1'+@sqlWhereEXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数--计算总页数select@totalPage=CEILING((@totalRecord+0.0)/@pageSize)set@sql='Select*FROM(selectROW_NUMBER()Over(orderbyt.author_timedesc)asrowId,t.*,s.column_type,s.column_name_english,s.column_save_path,casewhenDateDiff(day,getdate(),dateadd(day,s.page_new_days,t.author_time))>0then1else0endis_newfromTable1tinnerjoinTable2son(t.column_id=s.column_idandt.language_id=s.language_id)wheret.flag=1'+@SqlWhere--处理页数超出范围情况if@PageIndex<=0Set@pageIndex=1if@pageIndex>@totalPageSet@pageIndex=@totalPage--处理开始点和结束点Declare@StartRecordintDeclare@EndRecordintset@StartRecord=(@pageIndex-1)*@pageSize+1set@EndRecord=@StartRecord+@pageSize-1--继续合成sql语句set@Sql=@Sql+')astwhererowIdbetween'+Convert(varchar(50),@StartRecord)+'and'+Convert(varchar(50),@EndRecord)print@sqlExec(@Sql)END
解决方案四：
我也想要，嘿嘿
解决方案五：
createprocselectPage(@indexint,--页索引@sizeint--页容量)asbegindeclare@startindexintdeclare@endindexintif(@index=1)set@startindex=@index*(@size-1)elseset@startindex=@index*(@size-1)+1set@endindex=@index*@sizeselect*from(selectrow_number()over(orderby'字段')asNum,*fromtablename)AStempwhereNumbetween@startindexand@endindexend
解决方案六：
该回复于2012-03-26 13:04:38被版主删除