SQL Server 2005 方案一:基礎版(ROW_NUMBER)
CREATE PROC up_Page2005
@TableName varchar(50),
@Fields varchar(5000) = '*',
@OrderField varchar(5000), -- 必填,支援多欄位
@SqlWhere varchar(5000) = NULL,
@PageSize int,
@PageIndex int = 1,
@TotalPage int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
DECLARE @sql nvarchar(4000), @totalRecord int;
-- 1. 計算總筆數
SET @sql = 'SELECT @totalRecord = COUNT(*) FROM ' + @TableName +
CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END;
EXEC sp_executesql @sql, N'@totalRecord int OUTPUT', @totalRecord OUTPUT;
SET @TotalPage = CEILING(@totalRecord * 1.0 / @PageSize);
-- 2. 組裝分頁查詢
SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + ') AS RowId, ' +
@Fields + ' FROM ' + @TableName +
CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END + ') t ' +
'WHERE RowId BETWEEN ' + CAST((@PageIndex-1)*@PageSize + 1 AS varchar) + ' AND ' +
CAST(@PageIndex*@PageSize AS varchar);
EXEC (@sql);
COMMIT TRAN;
RETURN @totalRecord;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
RETURN -1;
END CATCH
END
方案二:優化版(支援傳入總筆數、避免重複計數)
CREATE PROC up_Page2005V2
@TableName varchar(50),
@Fields varchar(5000) = '*',
@OrderField varchar(5000),
@SqlWhere varchar(5000) = NULL,
@PageSize int,
@PageIndex int = 1,
@TotalRecord int = 0, -- 傳入已知總數可跳過 COUNT(*)
@TotalPage int OUTPUT
AS
BEGIN
-- 邏輯同方案一,前面加上 IF @TotalRecord <= 0 BEGIN ... 計算總數 END
-- 其餘相同
END
方案三:支援 JOIN 多表查詢
CREATE PROC up_Page2005V2_Join
@TableName varchar(150), -- 可傳入 "TableA a JOIN TableB b ON a.id=b.aid"
@Fields varchar(5000) = '*',
@OrderField varchar(5000),
@SqlWhere varchar(5000) = NULL,
@PageSize int,
@PageIndex int = 1,
@TotalRecord int = 0,
@TotalPage int OUTPUT
AS
BEGIN
-- 計算總數與分頁查詢同方案二,但 FROM 子句直接使用 @TableName 參數
-- 注意:@OrderField 需包含別名前綴,如 a.CreateTime
END
SQL Server 2000 相容版本(無 ROW_NUMBER)
CREATE PROC up_Page2000
@TableName varchar(50), @Fields varchar(5000), @OrderField varchar(5000),
@SqlWhere varchar(5000), @PageSize int, @PageIndex int,
@TotalRecord int OUTPUT, @TotalPage int OUTPUT
AS
BEGIN
-- 使用臨時表 + IDENTITY 模擬行號
DECLARE @sql nvarchar(4000), @start int, @end int;
SET @sql = 'SELECT IDENTITY(int,1,1) AS RowId, ' + @Fields + ' INTO #TmpPage FROM ' + @TableName +
CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' WHERE ' + @SqlWhere END +
' ORDER BY ' + @OrderField;
EXEC (@sql);
SELECT @TotalRecord = COUNT(*) FROM #TmpPage;
SET @TotalPage = CEILING(@TotalRecord * 1.0 / @PageSize);
SET @start = (@PageIndex-1)*@PageSize + 1;
SET @end = @PageIndex*@PageSize;
EXEC ('SELECT ' + @Fields + ' FROM #TmpPage WHERE RowId BETWEEN ' + @start + ' AND ' + @end);
DROP TABLE #TmpPage;
END
附錄:二分法分頁(適用於有連續主鍵的大表)
CREATE PROC up_PageBinary
@TableName varchar(50), @Fields varchar(5000), @OrderField varchar(5000),
@SqlWhere varchar(5000), @PageSize int, @PageIndex int
AS
BEGIN
DECLARE @low int, @high int, @mid int, @sql nvarchar(4000);
-- 先取得主鍵最小/最大值
SET @sql = 'SELECT @low=MIN('+@OrderField+'), @high=MAX('+@OrderField+') FROM '+@TableName+
CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' WHERE '+@SqlWhere END;
EXEC sp_executesql @sql, N'@low int OUTPUT, @high int OUTPUT', @low OUTPUT, @high OUTPUT;
-- 二分法定位起始主鍵值
WHILE @low < @high
BEGIN
SET @mid = (@low + @high) / 2;
SET @sql = 'SELECT COUNT(*) FROM '+@TableName+' WHERE '+@OrderField+' <= '+CAST(@mid AS varchar)+
CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' AND '+@SqlWhere END;
EXEC sp_executesql @sql, N'@cnt int OUTPUT', @cnt OUTPUT;
IF @cnt < (@PageIndex-1)*@PageSize SET @low = @mid + 1 ELSE SET @high = @mid;
END
-- 最後取一頁
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS varchar) + ' ' + @Fields + ' FROM ' + @TableName +
' WHERE ' + @OrderField + ' >= ' + CAST(@low AS varchar) +
CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' AND '+@SqlWhere END +
' ORDER BY ' + @OrderField;
EXEC (@sql);
END
選型建議
- SQL Server 2005+:**優先使用方案二**(可傳入總數、效能最佳)。
- 需 JOIN 多表:**方案三**。
- SQL Server 2000:方案四(臨時表)或二分法(主鍵連續、分布均勻時極快)。
- **避免動態 SQL 注入**:@TableName、@OrderField、@SqlWhere 來自用戶輸入時**必須嚴格白名單驗證**。