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

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

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
)

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