用存储过程实现分页显示

在存储过程里实现分页的逻辑，爽是爽，但造成与数据库耦合和压力，这是它最明显的缺点，但是，在某些情况，存储过程分页，依然是最有效的选择。

一下是sql server中实现的代码。

- File: dbo.SingleTablePager
-- Date: 2007-09-29 AM 11:09
-- Desc: 单表分页函数
if exists(select * from sysobjects where name = N'SingleTablePager' and type = 'FN')
 DROP FUNCTION dbo.SingleTablePager
GO 
CREATE FUNCTION dbo.SingleTablePager
(
 @Key VARCHAR(32), --用于分页的key
 @DbTableName VARCHAR(32), --读取数据的数据表
 @FldNames VARCHAR(256), --读取的字段列表
 @WhrPattern VARCHAR(512), --where后面的条件列表 （包括where）
 @Sort VARCHAR(4), --排序方法，只能是desc和asc
 @SortPattern VARCHAR(64), --排序的sql代码片断（包括order by）
 @PageIndex INT, --当前页码
 @PageSize INT --每页多少条数据
) RETURNS VARCHAR(1024)
AS
BEGIN
 DECLARE @Sql VARCHAR(1024)
 SET @Sql = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @FldNames + ' FROM ' + @DbTableName + ' '
 
 IF @PageIndex = 1
  SET @Sql = @Sql + @WhrPattern
 ELSE
 BEGIN
  IF LOWER(@Sort) = 'desc'
  BEGIN
   IF @WhrPattern <> ''
    SET @Sql = @Sql + @WhrPattern + 'AND ' + @Key + ' < ('
   ELSE
    SET @Sql = @Sql + 'WHERE ' + @Key + ' < ('
   SET @Sql = @Sql + 'SELECT MIN(' + @Key + ') FROM (SELECT TOP ' + CAST((@PageIndex - 1) * @PageSize AS VARCHAR) + ' ' + @Key + ' FROM ' + @DbTableName + ' ' + @WhrPattern + ' ' + @SortPattern + ') AS TempTable) '
  END
  ELSE
  BEGIN
   IF @WhrPattern <> ''
    SET @Sql = @Sql + @WhrPattern + 'AND ' + @Key + ' > ('
   ELSE
    SET @Sql = @Sql + 'WHERE ' + @Key + ' > ('
   SET @Sql = @Sql + 'SELECT MAX(' + @Key + ') FROM (SELECT TOP ' + CAST((@PageIndex - 1) * @PageSize AS VARCHAR) + ' ' + @Key + ' FROM ' + @DbTableName + ' ' + @WhrPattern + ' ' + @SortPattern + ') AS TempTable) '   
  END
 END 
 SET @Sql = @Sql + @SortPattern
 
 RETURN @Sql
END
GO 

运用实例：

- File: dbo.Woruhe_QeuryAllNoApprovedPrimaryPhotos
-- Date: 2007-12-12 3:18 PM
-- Desc: 分页显示所有等待中的主照片
if exists(select * from sysobjects where name = ('Woruhe_QeuryAllNoApprovedPrimaryPhotos') and type = 'P')
 DROP PROCEDURE dbo.Woruhe_QeuryAllNoApprovedPrimaryPhotos
GO
CREATE PROCEDURE dbo.Woruhe_QeuryAllNoApprovedPrimaryPhotos
(
 @PageIndex INT,
 @PageSize INT
)
AS
BEGIN
 DECLARE @Sql NVARCHAR(1024)
 DECLARE @WhereSql VARCHAR(512)
 SET @WhereSql = 'WHERE IsPubliclyRated = 0 and IsPrimary = 1'
 
 SET @Sql = dbo.SingleTablePager('PhotoID', 'PhotoApproveQueue', '*', @WhereSql, 'DESC', 'ORDER BY PhotoID DESC', @PageIndex, @PageSize)
 --EXEC(@Sql)
 EXEC  (@Sql)
 
 SET @Sql = 'SELECT COUNT(1) FROM PhotoApproveQueue ' + @WhereSql
 Exec  (@Sql)
END
GO