首页 > 数据库 > 其他综合 >

分页存储过程实例剖析心得

2011-10-15

最近修改了个分页存储过程,作为菜鸟,还是从中获益良多,这里就开始今天的分页之旅了。1.开始还原 下面先看一下原来的分页存储过程。1 ALTER PROCEDURE [dbo].[sp_Sql_Paging]2 (3 @SqlDataTable NVARCH...

最近修改了个分页存储过程,作为菜鸟,还是从中获益良多,这里就开始今天的分页之旅了。
1.开始还原 下面先看一下原来的分页存储过程。

1 ALTER PROCEDURE [dbo].[sp_Sql_Paging]
2 (
3 @SqlDataTable NVARCHAR(4000), -- 表名
4 @PrimaryKey NVARCHAR(4000), -- 主键名称
5 @Fields NVARCHAR(4000), -- 要返回的字段
6 @pageSize INT, -- 页尺寸
7 @pageIndex INT, -- 页码
8 @recordCount INT OUTPUT, -- 记录总数
9 @strOrderBy NVARCHAR(4000), -- 排序
10 @strWhere NVARCHAR(4000) -- 查询条件
11 )
12 AS
13 BEGIN
14 SET NOCOUNT ON
15 DECLARE @strSQL1 NVARCHAR(4000) -- SQL语句1
16 DECLARE @strSQL2 NVARCHAR(4000) -- SQL语句2
17
18 -- 创建临时表
19 -- 用来保存表的编号以及主键
20 CREATE TABLE #Temp_Paging
21 (
22 Temp_Paging_Id INT,
23 RowNumber INT
24 )
25 SET @strSQL1 = 'INSERT INTO [#Temp_Paging](Temp_Paging_Id, RowNumber) SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere
26 EXEC SP_EXECUTESQL @strSQL1
27 SET @recordCount = @@ROWCOUNT -- 取得总记录数
28
29 -- 判断页索引
30 IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
31 BEGIN
32 SET @pageIndex = 1
33 END
34
35 -- 分页查询
36 SET @strSQL2 = &#39;SELECT &#39; + @Fields + &#39; FROM &#39; + @SqlDataTable + &#39; WHERE &#39; + @PrimaryKey + &#39; IN (SELECT Temp_Paging_Id FROM [#Temp_Paging] WHERE RowNumber BETWEEN &#39; + Str((@pageIndex - 1) * @pageSize + 1) + &#39; AND &#39; + Str(@pageIndex * @pageSize) + &#39;) &#39; + @strOrderBy
37 EXEC SP_EXECUTESQL @strSQL2
38 DROP TABLE #Temp_Paging -- 删除临时表
39 END
从原分页存储过程很容易看出,这里运用了临时表保存编号,然后在通过pageIndex和pageSize计算所得,进行分页。
因为这里还以主键作为查询条件,故临时表中也保存了主键值。
很显然,这里的临时表无法做到通用,因为主键的类型不一定是上面定义的INT型,也可以是其它的类型,比如:uniqueidentifier(全球唯一标识)。
这样的话,这个存储过程就碰到了问题,所以必须进行改进。
2.思路一
思路一很简单,那就把这个类型声明成一个变量,然后通过系统表获取表的主键类型,再赋给变量不就可以了吗。看起来很美妙,不妨试一试先。
我们可以插入下面一段代码:

1 DECLARE @colType NVARCHAR(50) --主键列类型
2 DECLARE @strSQL3 NVARCHAR(500) --创建临时表语句
3
4 SET @colType = (SELECT typ.name as 数据类型
5 FROM sys.columns col
6 left join sys.types typ
7 on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id)
8 WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = @SqlDataTable)
9 and exists
10 ( SELECT 1 FROM sys.indexes idx
11 join sys.index_columns idxCol
12 on (idx.object_id = idxCol.object_id)
13 WHERE idx.object_id = col.object_id
14 AND idxCol.index_column_id = col.column_id
15 AND idx.is_primary_key = 1
16 ))
17
18 SET @strSQL3 = &#39;CREATE TABLE #Temp_Paging
19 (
20 Temp_Paging_Id &#39;+ @colType+&#39;,
21 RowNumber INT
22 )&#39;
23 PRINT @strSQL3
24 --EXEC(@strSQL3)
打印结果:
View Code
1 CREATE TABLE #Temp_Paging
2 (
3 Temp_Paging_Id uniqueidentifier,
4 RowNumber INT
5 )
很显然我们得到了所需要的临时表。这时我很开心,因为得到了我想要的东西。但似乎还没有结束,我要执行下该存储过程。
坏结果往往在这时出现:

这里就出现奇怪的事了,按打印出来的明明是正确的创建临时表语句,而且也执行了,为什么接下来对临时表的操作又是无效的了?
找资料问同事,终于明白,原来临时表分本地临时表和全局临时表。本地临时表需要注意实际删除的时间。
这里说得简单一点:当在用EXEC(@strSQL3) 创建临时表的时候,同时已经删除了临时表。因为EXEC这个过程的会话已经结束,临时表就被删除了。
这里有一篇博文做了更具体的解释,大家可以参考:点滴在心头_SQL临时表
3.思路二
上面的思路似乎行不通,那不如再换个思路,干脆不要建立临时表,用Select * from (select * from table) as temptable 这种思路代替创建临时表。
代码如下:

1 ALTER PROCEDURE [dbo].[sp_Sql_Paging]
2 (
3 @SqlDataTable NVARCHAR(4000), -- 表名
4 @PrimaryKey NVARCHAR(4000), -- 主键名称
5 @Fields NVARCHAR(4000), -- 要返回的字段
6 @pageSize INT, -- 页尺寸
7 @pageIndex INT, -- 页码
8 @recordCount INT OUTPUT, -- 记录总数
9 @strOrderBy NVARCHAR(4000), -- 排序
10 @strWhere NVARCHAR(4000) -- 查询条件
11 )
12 AS
13 BEGIN
14 SET NOCOUNT ON
15 DECLARE @strSQL1 NVARCHAR(4000) -- SQL语句1
16 DECLARE @strSQL2 NVARCHAR(4000) -- SQL语句2
17 DECLARE @strSQL3 NVARCHAR(4000) -- SQL语句3
18
19 SET @strSQL1 = &#39;SELECT &#39; + @PrimaryKey + &#39;, ROW_NUMBER() OVER (&#39; + @strOrderBy + &#39;) AS RowNumber FROM &#39; + @SqlDataTable + &#39; &#39; + @strWhere
20
21 --获取总记录数
22 SET @strSQL3 = &#39;SELECT @recordCount = COUNT(*) FROM &#39; + @SqlDataTable + &#39; &#39; + @strWhere
23 EXEC SP_EXECUTESQL
24 @stmt = @strSQL3,
25 @params = N&#39;@recordCount AS INT OUTPUT&#39;,
26 @recordCount = @recordCount OUTPUT
27
28 --分页查询
29 IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
30 BEGIN
31 SET @pageIndex = 1
32 END
33 SET @strSQL2 = &#39;SELECT &#39; + @Fields + &#39; FROM &#39; + @SqlDataTable + &#39; WHERE &#39; + @PrimaryKey + &#39; IN (SELECT &#39;+@PrimaryKey+&#39; FROM (&#39;+@strSQL1+&#39;) TempTable WHERE RowNumber BETWEEN &#39; + Str((@pageIndex - 1) * @pageSize + 1) + &#39; AND &#39; + Str(@pageIndex * @pageSize) + &#39;) &#39; + @strOrderBy
34 EXEC SP_EXECUTESQL @strSQL2
35 END
这里有个小知识点,注意EXEC SP_EXECUTESQL的写法以及和EXEC(@strsql)的区别。大家可以去找资料了解下。
关于分页的一些事就写到这了,仅供参考。
-------------------------------------------欢迎大家指摘--------------------------------------------------------------

作者:WILLPAN

相关文章
最新文章
热点推荐