存储过程分页

CREATE    proc [dbo].[EX_SP_GetTopicList]
       @intPageNo int,  --页号
       @intPageSize int, --每页显示数
       @RecordCount int OUTPUT  --总记录数(存储过程输出参数
as

DECLARE @StrSQL VARCHAR(5000)
declare @PredCount VARCHAR(20)

set nocount on

set @PredCount=CONVERT(VARCHAR(20),(@intPageNo-1) * @intPageSize)

--计算出记录总数
SELECT @RecordCount=COUNT(ID) FROM EX_POLICYRULE

--创建临时表
CREATE TABLE #TMPTABLE
(
[ID] int NOT NULL
)

--筛选出已经展示过的信息
SET @StrSQL='INSERT INTO #TMPTABLE SELECT TOP '+@PredCount +' [ID]
FROM EX_POLICYRULE
ORDER BY PUB_DATE DESC'

EXEC(@StrSQL)

--筛选出当前页所要展示的信息
SET @StrSQL='SELECT TOP '+CONVERT(VARCHAR(20),@intPageSize)+' [ID],INFO_ID,INFOTITLE,BBSJ,BBDW,PUB_DATE
FROM EX_POLICYRULE
WHERE [ID] NOT IN (SELECT [ID] FROM #TMPTABLE)
ORDER BY PUB_DATE DESC'

EXEC(@StrSQL)

--删除临时表
DROP TABLE #TMPTABLE