存储过程分页

View Code
 1 CREATE    proc [dbo].[EX_SP_GetTopicList] 
 2        @intPageNo int,  –页号
 3        @intPageSize int, –每页显示数
 4        @RecordCount int OUTPUT  –总记录数(存储过程输出参数
 5 as
 6  
 7 DECLARE @StrSQL VARCHAR(5000)
 8 declare @PredCount VARCHAR(20)
 9 
10 set nocount on
11 
12 set @PredCount=CONVERT(VARCHAR(20),(@intPageNo-1) * @intPageSize)
13 
14 –计算出记录总数
15 SELECT @RecordCount=COUNT(ID) FROM EX_POLICYRULE
16 
17 –创建临时表
18 CREATE TABLE #TMPTABLE
19 (
20 [ID] int NOT NULL
21 )
22 
23 –筛选出已经展示过的信息
24 SET @StrSQL=’INSERT INTO #TMPTABLE SELECT TOP ‘+@PredCount +’ [ID] 
25 FROM EX_POLICYRULE 
26 ORDER BY PUB_DATE DESC’
27 
28 EXEC(@StrSQL)
29 
30 –筛选出当前页所要展示的信息
31 SET @StrSQL=’SELECT TOP ‘+CONVERT(VARCHAR(20),@intPageSize)+’ [ID],INFO_ID,INFOTITLE,BBSJ,BBDW,PUB_DATE
32 FROM EX_POLICYRULE
33 WHERE [ID] NOT IN (SELECT [ID] FROM #TMPTABLE)
34 ORDER BY PUB_DATE DESC’
35 
36 EXEC(@StrSQL)
37 
38 –删除临时表
39 DROP TABLE #TMPTABLE