|
感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！
代码如下:
/*?
数据库分页存储过程，支持倒序和升序?
参数说明：?
??@tablename：为搜索表名?
??@tablefield：为表的字段，约定为表的主键,?
??@where：为搜索表名，要显示所有记录请设为"1=1"?
??@orderby：为搜索结果排序，如order?by?id?desc?
??@fieldlist：为字段列表，如userid,?username?
??@curpage：当前页码?
??@page_record：每页记录条数?
??@Sort：排序标识（如果是倒序排，参数值为desc，为升序，参数值为asc，跟orderby参数是对应的）?
结果：?返回表tablename中满足条件where的第curpage页的page_record条记录，结果按orderby排序?
*/?
CREATE?PROCEDURE?proc_CommonPaging?
@tablename?varchar(100),?
@tablefield?varchar(20),?
@where?varchar(5000),?
@orderby?varchar(500),?
@fieldlist?varchar(1000),?
@curpage?int,?
@page_record?int,?
@sort?varchar(8)?
AS?
BEGIN?
??DECLARE?@cmd?varchar(8000)?
??DECLARE?@uprecord?int?
??DECLARE?@Op?varchar(2)?--?操作符?
??DECLARE?@max_min?varchar(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?=?'SELECT?TOP?'+cast(@page_record?AS?NVARCHAR)+'?'+@fieldlist+'?FROM?'+@tablename+'?WHERE?'+@where+'?'+@orderby?
??ELSE?
????SET?@cmd?=?'SELECT?TOP?'+cast(@page_record?AS?NVARCHAR)+'?'+@fieldlist+'?FROM?'+@tablename+'?WHERE?'+@where+'?AND?'+@tablefield+'??
????'+@op+'?(SELECT?'+@max_min+'('+@tablefield+')??FROM?(SELECT?TOP??'+cast(@uprecord?AS?NVARCHAR)+'?'+@tablefield+'?FROM?'+@tablename+'?WHERE??
????'+@where+'?'+@orderby+')?AS?TmpTbl?)?AND?'+@where+'?'+@orderby?
??SET?@cmd?=?@cmd?+?';?SELECT?COUNT(*)?FROM?'+@tablename+'?WHERE?'+@where?
??EXEC(@cmd)?
??PRINT(@cmd)?
END?
GO?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
