*
数据库分页存储过程,支持倒序和升序
参数说明:
@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
数据库分页存储过程,支持倒序和升序
参数说明:
@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