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
View Code
