asp.net 分页存储过程实例剖析心得 |
|
本文标签：分页存储过程 1.开始还原 下面先看一下原来的分页存储过程 。 复制代码 代码如下: ALTER PROCEDURE [dbo].[sp_Sql_Paging] ( @SqlDataTable NVARCHAR(4000), -- 表名 @PrimaryKey NVARCHAR(4000), -- 主键名称 @Fields NVARCHAR(4000), -- 要返回的字段 @pageSize INT, -- 页尺寸 @pageIndex INT, -- 页码 @recordCount INT OUTPUT, -- 记录总数 @strOrderBy NVARCHAR(4000), -- 排序 @strWhere NVARCHAR(4000) -- 查询条件 ) AS BEGIN SET NOCOUNT ON DECLARE @strSQL1 NVARCHAR(4000) -- SQL语句1 DECLARE @strSQL2 NVARCHAR(4000) -- SQL语句2 -- 创建临时表 -- 用来保存表的编号以及主键 CREATE TABLE #Temp_Paging ( Temp_Paging_Id INT, RowNumber INT ) SET @strSQL1 = INSERT INTO [#Temp_Paging](Temp_Paging_Id, RowNumber) SELECT + @PrimaryKey + , ROW_NUMBER() OVER ( + @strOrderBy + ) AS RowNumber FROM + @SqlDataTable + + @strWhere EXEC SP_EXECUTESQL @strSQL1 SET @recordCount = @@ROWCOUNT -- 取得总记录数 -- 判断页索引 IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize BEGIN SET @pageIndex = 1 END -- 分页查询 SET @strSQL2 = SELECT + @Fields + FROM + @SqlDataTable + WHERE + @PrimaryKey + IN (SELECT Temp_Paging_Id FROM [#Temp_Paging] WHERE RowNumber BETWEEN + Str((@pageIndex - 1) * @pageSize + 1) + AND + Str(@pageIndex * @pageSize) + ) + @strOrderBy EXEC SP_EXECUTESQL @strSQL2 DROP TABLE #Temp_Paging -- 删除临时表 END 从原分页存储过程很容易看出，这里运用了临时表保存编号，然后在通过pageIndex和pageSize计算所得，进行分页 。 因为这里还以主键作为查询条件，故临时表中也保存了主键值 。 很显然，这里的临时表无法做到通用，因为主键的类型不一定是上面定义的INT型，也可以是其它的类型，比如：uniqueidentifier(全球唯一标识) 。 这样的话，这个存储过程就碰到了问题，所以必须进行改进 。 2.思路一 思路一很简单，那就把这个类型声明成一个变量，然后通过系统表获取表的主键类型，再赋给变量不就可以了吗 。看起来很美妙，不妨试一试先 。 我们可以插入下面一段代码： 复制代码 代码如下: DECLARE @colType NVARCHAR(50) --主键列类型 DECLARE @strSQL3 NVARCHAR(500) --创建临时表语句 SET @colType = (SELECT typ.name as 数据类型 FROM sys.columns col left join sys.types typ on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id) WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = @SqlDataTable) and exists ( SELECT 1 FROM sys.indexes idx join sys.index_columns idxCol on (idx.object_id = idxCol.object_id) WHERE idx.object_id = col.object_id AND idxCol.index_column_id = col.column_id AND idx.is_primary_key = 1 )) SET @strSQL3 = CREATE TABLE #Temp_Paging ( Temp_Paging_Id + @colType+, RowNumber INT ) PRINT @strSQL3 --EXEC(@strSQL3) 打印结果： 复制代码 代码如下: CREATE TABLE #Temp_Paging ( Temp_Paging_Id uniqueidentifier, RowNumber INT ) 很显然我们得到了所需要的临时表 。这时我很开心，因为得到了我想要的东西 。但似乎还没有结束，我要执行下该存储过程 。 坏结果往往在这时出现：  这里就出现奇怪的事了，按打印出来的明明是正确的创建临时表语句，而且也执行了，为什么接下来对临时表的操作又是无效的了？ 找资料问同事，终于明白，原来临时表分本地临时表和全局临时表 。本地临时表需要注意实际删除的时间 。 这里说得简单一点：当在用EXEC(@strSQL3) 创建临时表的时候，同时已经删除了临时表 。因为EXEC这个过程的会话已经结束，临时表就被删除了 。 这里有一篇博文做了更具体的解释，大家可以参考：点滴在心头_SQL临时表 3.思路二 上面的思路似乎行不通，那不如再换个思路，干脆不要建立临时表，用Select * from (select * from table) as temptable 这种思路代替创建临时表 。 代码如下： 复制代码 代码如下: ALTER PROCEDURE [dbo].[sp_Sql_Paging] ( @SqlDataTable NVARCHAR(4000), -- 表名 @PrimaryKey NVARCHAR(4000), -- 主键名称 @Fields NVARCHAR(4000), -- 要返回的字段 @pageSize INT, -- 页尺寸 @pageIndex INT, -- 页码 @recordCount INT OUTPUT, -- 记录总数 @strOrderBy NVARCHAR(4000), -- 排序 @strWhere NVARCHAR(4000) -- 查询条件 ) AS BEGIN SET NOCOUNT ON DECLARE @strSQL1 NVARCHAR(4000) -- SQL语句1 DECLARE @strSQL2 NVARCHAR(4000) -- SQL语句2 DECLARE @strSQL3 NVARCHAR(4000) -- SQL语句3 SET @strSQL1 = SELECT + @PrimaryKey + , ROW_NUMBER() OVER ( + @strOrderBy + ) AS RowNumber FROM + @SqlDataTable + + @strWhere --获取总记录数 SET @strSQL3 = SELECT @recordCount = COUNT(*) FROM + @SqlDataTable + + @strWhere EXEC SP_EXECUTESQL @stmt = @strSQL3, @params = N@recordCount AS INT OUTPUT, @recordCount = @recordCount OUTPUT --分页查询 IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize BEGIN SET @pageIndex = 1 END SET @strSQL2 = SELECT + @Fields + FROM + @SqlDataTable + WHERE + @PrimaryKey + IN (SELECT +@PrimaryKey+ FROM (+@strSQL1+) TempTable WHERE RowNumber BETWEEN + Str((@pageIndex - 1) * @pageSize + 1) + AND + Str(@pageIndex * @pageSize) + ) + @strOrderBy EXEC SP_EXECUTESQL @strSQL2 END 这里有个小知识点，注意EXEC SP_EXECUTESQL的写法以及和EXEC(@strsql)的区别 。大家可以去找资料了解下 。 关于分页的一些事就写到这了，仅供参考 。 |
