分页的存储过程(SQLSERVER2000)

--===============================================================
--
--分页的存储过程
--
--===============================================================
CREATE  PROCEDURE GetDividends
(
@PageNumber INT,     --页码
@PageSize INT,      --每页大小
@CountDividends INT OUTPUT    --数据总数
)

AS

-- 定义一个Table变量
DECLARE @Dividends TABLE(
--类型一定要自动递增
RowNumber SMALLINT IDENTITY (1, 1) NOT NULL ,      
SRC int not null,
PERMID int not null,
DATADATE int not null,
RESET_DT int not null,
CHEQV float(8),
CURCCDV char(3),
DIV float(8),
DIVOP float(8),
DIVRATE float(8),
PAYDATE int,
RECDATE int,
ADJTYPE char(12)

)

-- 把数据读到刚定义的@Dividends中

INSERT INTO @Dividends (SRC ,PERMID ,DATADATE ,RESET_DT ,CHEQV ,CURCCDV ,DIV ,DIVOP ,DIVRATE ,PAYDATE ,RECDATE ,ADJTYPE )
SELECT SRC ,PERMID ,DATADATE ,RESET_DT ,CHEQV ,CURCCDV ,DIV ,DIVOP ,DIVRATE ,PAYDATE ,RECDATE ,ADJTYPE
FROM fe_sec_divid
-- 返回产品数

SELECT @CountDividends = COUNT(*) FROM #Dividends

-- 返回请求页面的数据

SELECT SRC ,PERMID ,DATADATE ,RESET_DT ,CHEQV ,CURCCDV ,DIV ,DIVOP ,DIVRATE ,PAYDATE ,RECDATE ,ADJTYPE
FROM @Dividends
WHERE RowNumber > (@PageNumber - 1) * @PageSize AND RowNumber <= @PageNumber * @PageSize