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

最近修改了个分页存储过程，作为菜鸟，还是从中获益良多，这里就开始今天的分页之旅了。

肃南裕固族自治ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！

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临时表

#p#

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)的区别。大家可以去找资料了解下。

关于分页的一些事就写到这了，仅供参考。

分享名称：分页存储过程实例剖析心得
文章地址：http://www.mswzjz.cn/qtweb/news44/151994.html

攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能，是专注品牌与效果的网络营销公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：贝锐智能

贝锐智能技术为您推荐以下文章