笔记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
