CREATE PROCEDURE Zkx_Ibcom_DevPage
@PageSize int, --每页的记录条数
@PageNumber int--当前页面
AS
/*
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
*/
Declare @SqlTable AS varchar(1000) --数据表
Declare @SqlFiled AS Varchar(1000) -- 数据字段
Declare @SqlWhere AS Varchar(1000) --数据查询条件
Declare @SqlText AS Varchar(1000) --SQL字符串
IF @PageNumber >1
BEGIN
SET @SqlTable=' test '
SET @SqlFiled='*'
SET @SqlWhere= ' (ID < (SELECT MIN(id) FROM (SELECT TOP '+CAST((@PageNumber-1)*@PageSize AS varchar(30)) + ' id FROM '+@SqlTable+' ORDER BY id desc) AS T))'
SET @SqlText='SELECT TOP '+CAST(@PageSize AS varchar(30)) + ' '+@SqlFiled+' FROM ' + @SqlTable +' where '+@SqlWhere +'ORDER BY ID DESC'
Exec(@SqlText)
END
ELSE
BEGIN
SET @SqlText= 'SELECT TOP '+ CAST(@PageSize AS varchar(30))+ ' * FROM TEST ORDER BY ID DESC '
Exec(@SqlText)
END
GO