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

最近修改了个分页存储过程，作为菜鸟，还是从中获益良多，这里就开始今天的分页之旅了。
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 = '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
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 = 'CREATE TABLE #Temp_Paging
19                 (
20                     Temp_Paging_Id '+ @colType+',
21                     RowNumber INT
22                 )'
23 PRINT @strSQL3
24 --EXEC(@strSQL3)
打印结果：
View Code
1 CREATE TABLE #Temp_Paging
2                 (
3                     Temp_Paging_Id uniqueidentifier,
4                     RowNumber INT
5                 )
很显然我们得到了所需要的临时表。这时我很开心，因为得到了我想要的东西。但似乎还没有结束，我要执行下该存储过程。
坏结果往