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

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