最近修改了个分页存储过程，作为菜鸟，还是从中获益良多，这里就开始今天的分页之旅了。
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) DECLARE @strSQL2 NVARCHAR(4000) 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 |
打印结果：
| CREATE TABLE #Temp_Paging ( Temp_Paging_Id uniqueidentifier, RowNumber INT ) |
