代码如下:/* 数据库分页存储过程,支持倒序和升序 参数说明: @tablename:为搜索表名 @tablefield:为表的字段,约定为表的主键, @where:为搜索表名,要显示所有记录请设为"1=1" @orderby:为搜索结果排序,如orderbyiddesc @fieldlist:为字段列表,如userid,username @curpage:当前页码 @page_record:每页记录条数 @Sort:排序标识(如果是倒序排,参数值为desc,为升序,参数值为asc,跟orderby参数是对应的) 结果:返回表tablename中满足条件where的第curpage页的page_record条记录,结果按orderby排序 */ CREATEPROCEDUREproc_CommonPaging @tablenamevarchar(100), @tablefieldvarchar(20), @wherevarchar(5000), @orderbyvarchar(500), @fieldlistvarchar(1000), @curpageint, @page_recordint, @sortvarchar(8) AS BEGIN DECLARE@cmdvarchar(8000) DECLARE@uprecordint DECLARE@Opvarchar(2)--操作符 DECLARE@max_minvarchar(4)--最大/最小计算 SET@op='<' SET@max_min='MIN' IF@sort='asc' BEGIN SET@Op='>' SET@max_min='MAX' END SET@uprecord=@curpage*@page_record IF@curpage=0 SET@cmd='SELECTTOP'+cast(@page_recordASNVARCHAR)+''+@fieldlist+'FROM'+@tablename+'WHERE'+@where+''+@orderby ELSE SET@cmd='SELECTTOP'+cast(@page_recordASNVARCHAR)+''+@fieldlist+'FROM'+@tablename+'WHERE'+@where+'AND'+@tablefield+' '+@op+'(SELECT'+@max_min+'('+@tablefield+')FROM(SELECTTOP'+cast(@uprecordASNVARCHAR)+''+@tablefield+'FROM'+@tablename+'WHERE '+@where+''+@orderby+')ASTmpTbl)AND'+@where+''+@orderby SET@cmd=@cmd+';SELECTCOUNT(*)FROM'+@tablename+'WHERE'+@where EXEC(@cmd) PRINT(@cmd) END GO
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|