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

1.开始还原下面先看一下原来的分页存储过程。复制代码代码如下： ALTER PRocESS[dbo].[sp _ SqL _ Paging](@ SqLDatatable NVARCHAR(4000)，-表名@PrimaryKey NVARCHAR(4000)，-主键名称@ field NVARCHAR(4000)，-要返回的字段@pageSize INT，-页尺寸@pageIndex INT，-页码@recordCount INT OUTPUT，-记录总数@strOrderBy NVARCHAR(4000)，-排序@strWhere NVARCHAR(4000) -查询条件)AS BEGIN SET no count ON DECLARE @ strsql 1 NVARCHAR(4000)-SQL语句1 DECLARE @ STrsql 2 NVARCHAR(4000)-SQL语句2 - 创建临时表-用来保存表的编号以及主键CREATE TABLE # Temp _ Paging(Temp _ Paging _ Id INT，行号INT)SET @ strsql 1=' INSERT in[# Temp _ Paging](Temp _ Paging _ Id，行号)SELECT ' @PrimaryKey '， ROW _ NUMBER()OVER(' @ strOrderBy ')AS ROW NUMBER FROM ' @ sqldatable ' ' @ strWhere EXEC SP _ EXECUTESQL @ strsql 1 SET @ recordCount=@ @ ROWCOUNT-取得总记录数-判断页索引IF @页面索引@记录计数* 1.0/@页面大小1.0 OR @ recordCount=@页面大小BEGIN SET @页面索引=1 END-分页查询SET @ strsql 2=' SELECT ' @ Fields ' FROM ' @ sqldata TABLE ' WHERE ' @主键' IN(SELECT Temp _ Paging _ Id FROM[# Temp _ Paging]WHERE行号BETWEEN ' Str(@ page index-1)* @ page size 1)' AND ' Str(@ page index * @ page size)')' @ strOrderBy EXEC SP _ EXECUTESQL @ strsql 2 DROP TABLE # Temp _ Paging-删除临时表结束从原分页存储过程很容易看出，这里运用了临时表保存编号，然后在通过页面索引和页面大小计算所得，进行分页。因为这里还以主键作为查询条件，故临时表中也保存了主键值。很显然，这里的临时表无法做到通用，因为主键的类型不一定是上面定义的（同国际组织)国际组织型，也可以是其它的类型，比如：uniqueidentifier(全球唯一标识)。 这样的话，这个存储过程就碰到了问题，所以必须进行改进。 2.思路一思路一很简单，那就把这个类型声明成一个变量，然后通过系统表获取表的主键类型，再赋给变量不就可以了吗。看起来很美妙，不妨试一试先。我们可以插入下面一段代码：复制代码代码如下： DECLARE @ ColtType NVARCHAR(50)-主键列类型DECLARE @strSQL3 NVARCHAR(500) -创建临时表语句SET @colType=(SELECT typ.name as数据类型来自系统。列列左联接系统。type on(col . system _ type _ id=typ。系统类型id和col . user _ type _ id=typ。user _ type _ id)WHERE列. object _ id=(SELECT object _ id FROM sys。tables WHERE name=@ sqldata表)并且存在(从系统索引idx中选择1联接系统。index _ columns idxCol on(idx。object _ id=idxCol。object _ id)WHERE idx。对象id=列对象id和idxCol SET @ strsql 3=' CREATE TABLE # Temp _ Paging(Temp _ Paging _ Id ' @ colType '，行号INT)' PRINT @ strsql 3-EXEC(@ strsql 3)打印结果：复制代码代码如下：创建表#临时分页很显然我们得到了所需要的临时表。这时我很开心，因为得到了我想要的东西。但似乎还没有结束，我要执行下该存储过程。坏结果往往在这时出现

有件奇怪的事。根据打印的创建临时表的语句，这显然是正确的，它已经被执行。为什么临时表上的操作又无效了？找资料问同事，终于意识到原来的临时表分为局部临时表和全局临时表。本地表需要注意实际的删除时间。简单来说，当用EXEC(@strSQL3)创建临时表时，临时表同时被删除。由于EXEC的会话已经结束，临时表已被删除。这里有一篇博文给出了更具体的解释。可以参考一下：咬在心里_SQL临时表3。第二个想法上面的想法似乎行不通，不如换个想法，不创建临时表，用select * from (select * from table)的想法作为临时表。代码如下：复制代码如下： alter procedure [dbo]。[sp _ SQL _ paging](@ sqldata table NVARCHAR(4000)，-表名@PrimaryKey NVARCHAR(4000)，-主键名@ fields nvarchar (4000)，-要返回的字段@ pagesizeint，-页面大小@ pageindexint，-页码@ recordcountint输出，-记录总数@strOrderBy NVARCHAR(4000)，-sort @ strwelenvarchar(4000)-查询条件)作为开始设置no count on declare @ strsql 1 varchar-SQL语句2 delete ate @ strsql 3n varchar(4000)-SQL语句3 set @ strsql 1=' select ' @ primary key '，Row _ number () over ('@ str order by ')作为行号从' @ sqldata table ' ' @ strhere-获取设置的记录总数@ strsql 3=' select @ record count=count(*)。FROM ' @ SqlDataTable ' ' @ strWhere EXEC SP _ EXECUTESQL @ stmt=@ strsql 3，@ params=N ' @ recordCount AS INT OUTPUT '， @ recordcount=@ recordcount输出-分页查询IF @ page index @ recordCount * 1.0/@ page size 1.0 OR @ recordCount=@ page size BEGIN SET @ page index=1 END SET @ strsql 2=' SELECT ' @ Fields ' FROM ' @ sqldatable ' WHERE ' @ PrimaryKey ' IN(SELECT '[emailprotected]' FROM([emailprotected]')TempTable WHERE row number BETWEEN ' Str(@ page index-1)* @ page size)和' str (@ pageindex * @ pagesize)') 可以找资料了解一下。这里写一些关于分页的事情，仅供参考。