笔记198 长均的分页存储过程和分页sql代码

 1 --长均的分页存储过程和分页sql代码
 2 --如果表里面有自增列的话，例如RecordNO 就可以用下面的分页sql 和分页存储过程
 3 
 4 
 5 
 6 
 7 --插入测试数据，假如在pratice库里建立好DP_OilCanOption表
 8 --测试的时候两列一列RecordNO，一列name
 9 USE [pratice]
10 DECLARE @a INT
11 SET @a=1
12 WHILE @a<21
13 BEGIN
14 INSERT [dbo].[DP_OilCanOption]( [Name] )
15 SELECT CAST(@a AS VARCHAR(10))+'你好'
16 SET @a=@a+1
17 END
18 
19 
20 ----------------------------------------------------------------------------
21 USE [pratice]
22 GO
23 
24 /*
25 @PageIndex 当前页码
26 @PageSize 每页的记录数
27 */    
28 CREATE PROCEDURE [dbo].[DP_OilCanOption_Get]    
29 (    
30 @PageIndex int,
31 @PageSize int
32 )    
33 AS
34      BEGIN
35           DECLARE @Page int
36           DECLARE @PageTotal int
37           DECLARE @CONSQL VARCHAR(4000)
38           SET @Page = (@PageIndex - 1)
39           SET @PageTotal=@Page*@PageSize
40           SELECT TOP (@PageSize)
41         *
42           INTO #PageIndex  FROM DP_OilCanOption WHERE RecordNO NOT IN (SELECT TOP (@PageTotal) RecordNO FROM DP_OilCanOption ORDER BY RecordNO)
43           SET @CONSQL='SELECT
44          *
45           FROM #PageIndex '
46           EXEC(@CONSQL)
47           DROP TABLE #PageIndex
48           SELECT COUNT(RecordNO)AS TotalRecords FROM DP_OilCanOption
49      END
50 
51 
52 
53 --------------------------------------------------------------------------------
54 --执行存储过程
55 USE [pratice]
56 EXEC [dbo].[DP_OilCanOption_Get] @PageIndex = 2, -- int
57     @PageSize = 5 -- int
58 
59 
60 
61 
62 -------------------------------分页sql---------------------------------------------
63 USE [pratice]
64 DECLARE @PageIndex int
65 DECLARE @PageSize INT
66 SET @PageIndex=1
67 SET @PageSize=100
68 
69 BEGIN
70      DECLARE @Page int
71      DECLARE @PageTotal int
72      DECLARE @CONSQL VARCHAR(4000)
73      SET @Page = (@PageIndex - 1)
74      SET @PageTotal=@Page*@PageSize
75      SELECT TOP (@PageSize)
76     *
77      INTO #PageIndex  FROM [pratice].[dbo].DP_OilCanOption WHERE RecordNO NOT IN (SELECT TOP (@PageTotal) RecordNO FROM DP_OilCanOption ORDER BY RecordNO)
78      SET @CONSQL='SELECT
79     *
80      FROM #PageIndex '
81      EXEC(@CONSQL)
82      DROP TABLE #PageIndex
83      SELECT COUNT(RecordNO)AS TotalRecords FROM DP_OilCanOption
84 END