我自己写的一个分页存储过程,请大家评评。
程序优点:
做到了调用方便、通用;
由于是把查询结果先存放到临时表中,所以,不需要主查询有主键;
个人测试,在2.0G、1G内存、80万条记录条件下,最长需要4秒时间;
想请高手给点指点,看看是否能够优化,或者提出更好的方案。
-------------------------------------
首先实现这个分页的通用存储过程:
--------------------------------------------------------------
-- 通用分页查询过程
-- exec p_PageRecordset_Table '#TempTable',20,1,100,0
--------------------------------------------------------------
CREATE PROCEDURE p_PageRecordset_Table
(
@TableName NVARCHAR(100),--全局临时表名称
@PageSize INT, --每页的记录条数
@PageNumber INT, --当前页面
@TotalSize INT, --总记录条数
@TotalPage INT OUTPUT --总页数
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SqlText AS NVARCHAR(1000)
-- 计算总页数
IF @PageSize>0
BEGIN
SET @TotalPage=@TotalSize/@PageSize
IF (@PageSize*@TotalPage)<>@TotalSize
SET @TotalPage=@TotalPage+1
END
ELSE
BEGIN
SET @TotalPage=1
END
-- 校正输入参数
IF @PageNumber<1 SET @PageNumber=1
IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage
IF @PageSize<=0 OR @TotalSize=0
BEGIN
--如果设置PageSize小于等于0,表示不分页。
SET @SqlText='
SELECT * FROM ' + @TableName + '
DROP TABLE ' + @TableName
END
ELSE
BEGIN
SET @SqlText='
SET NOCOUNT ON
SELECT * FROM ' + @TableName + '
WHERE
' + CAST((@PageNumber-1)*@PageSize+1 AS NVARCHAR(30)) + '<=ROWNUM and ROWNUM<=' + CAST(@PageNumber*@PageSize AS NVARCHAR(30)) + '
ORDER BY ROWNUM
DROP TABLE ' + @TableName
END
-- PRINT @SqlText
EXEC(@SqlText)
End
GO
-------------------------------------
调用实例:
DECLARE @PageSize INT
DECLARE @PageNumber INT
DECLARE @TotalSize INT
DECLARE @TotalPage INT
SET @PageSize=5
SET @PageNumber=1
SET NOCOUNT ON
-- 把查询的结果放到一个临时表中,供分页处理。
SELECT
CAST(EmployeeID as INT) AS EmployeeID, -- 如果源表中有自编号的字段,要转换一下。
LastName,
FirstName,
BirthDate,
Address,
Region,
PostalCode,
Country,
IDENTITY(INT,1,1) AS ROWNUM -- 添加一个新的编号列,供分页存储过程使用。
INTO #tbl_p_Page_List -- 可以随机生成一个临时表名
FROM Employees
ORDER BY LastName,FirstName
--------------------------------------------------
-- 以下是通用的分页过程
--------------------------------------------------
--取得总记录条数
SELECT @TotalSize=@@ROWCOUNT
-- 调用通用的分页处理过程
-- 注意:临时表中必须有自动编号的字段 IDENTITY(INT,1,1) AS ROWNUM
EXEC p_PageRecordset_Table '#tbl_p_Page_List',
@PageSize,@PageNumber,@TotalSize,@TotalPage OUTPUT
--------------------------------------------------
PRINT @TotalSize
PRINT @TotalPage
GO