如何编写SQL Server 2005中的存储过程以实现分页功能？

分页存储过程

在SQL Server 2005中，分页功能可以通过使用ROW_NUMBER()函数来实现，这个函数为结果集中的每一行分配一个唯一的序号，使得数据可以按页进行查询和显示，以下是一个通用的分页存储过程示例，该过程支持自定义表名、字段选择、排序条件以及查询条件。

存储过程定义

 ALTER PROCEDURE [dbo].[mypage]     @TableName NVARCHAR(50),            表名     @ReFieldsStr NVARCHAR(200) = '*',   字段名(全部字段为*)     @OrderString NVARCHAR(200),         排序字段(必须支持!多字段不用加order by)     @WhereString NVARCHAR(500) = '',     条件语句(不用加where)     @PageSize INT,                       每页多少条记录     @PageIndex INT = 1,                  指定为第几为当前页     @TotalRecord INT OUTPUT              返回总记录数 AS BEGIN     DECLARE @StartRecord INT, @EndRecord INT;     SET @StartRecord = (@PageIndex 1) * @PageSize + 1;     SET @EndRecord = @StartRecord + @PageSize 1;     DECLARE @TotalCountSql NVARCHAR(500);     SET @TotalCountSql = N'SELECT @TotalRecord = count(*) FROM ' + @TableName;     IF @WhereString <> '' AND @WhereString IS NOT NULL     BEGIN         SET @TotalCountSql = @TotalCountSql + ' WHERE ' + @WhereString;         SET @SqlString = @SqlString + ' WHERE ' + @WhereString;     END     DECLARE @SqlString NVARCHAR(2000);     SET @SqlString = N'SELECT * FROM (SELECT row_number() OVER (ORDER BY ' + @OrderString + ') as rowId, ' + @ReFieldsStr + ' FROM ' + @TableName + ') AS t WHERE rowId BETWEEN ' + CAST(@StartRecord AS NVARCHAR) + ' AND ' + CAST(@EndRecord AS NVARCHAR);     EXEC sp_executesql @SqlString, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT; END

参数说明

@TableName: 要查询的表名。

@ReFieldsStr: 要返回的字段列表，默认为表示所有字段。

@OrderString: 排序字段，支持多个字段，不需要加ORDER BY关键字。

@WhereString: 查询条件，不带WHERE关键字。

@PageSize: 每页显示的记录数。

@PageIndex: 当前页码，默认为1。

@TotalRecord: 输出参数，用于返回总记录数。

使用示例

以下是一个使用上述存储过程的ASP代码示例：

 <%%= Response.Write("请输入页码") %> <% page="" If Request.QueryString("page") <> "" Then page=Request.QueryString("page") %> <%= page %> <% If page = "" Then page=1 ElseIf IsNumeric(page) = False Then page=1 %> <% End If %> <% Maxperpage=100 %> <% totalcount=1 %> <% Set objcmd=Server.CreateObject("ADODB.Command") %> <% Set objrds=Server.CreateObject("ADODB.Recordset") %> <% objcmd.ActiveConnection=objconn %> <% objcmd.CommandText="mypage" %> <% objcmd.Parameters.Append objcmd.CreateParameter("@TableName",200,2,"Cards") %> <% objcmd.Parameters.Append objcmd.CreateParameter("@ReFieldsStr",200,2,"cardid,cardpsw,cardname") %> <% objcmd.Parameters.Append objcmd.CreateParameter("@OrderString",200,2,"id") %> <% objcmd.Parameters.Append objcmd.CreateParameter("@WhereString",200,2,"") %> <% objcmd.Parameters.Append objcmd.CreateParameter("@pageSize",3,1,Maxperpage) %> <% objcmd.Parameters.Append objcmd.CreateParameter("@PageIndex",3,1,page) %> <% objcmd.Parameters.Append objcmd.CreateParameter("@totalrecord",3,2,totalcount) %> <% objrds.Open objcmd.Execute(),,adOpenStatic,adLockReadOnly %> <% While not objrds.eof And totalcount=1 %> <% Response.Write("<li>"& cardid & "</li>") %> <% objrds.MoveNext %> <% Wend %> <% IF totalcount=1 Then %> <% EXEC sp_executesql @totalCountSql,N'@TotalRecord INT OUTPUT',@TotalRecord OUTPUT %> <% End If %> <% Response.Write("<p>总记录数："& totalcount &"</p>") %>

常见问题解答（FAQ）

Q1: SQL Server 2005中的分页存储过程如何实现？

A1: SQL Server 2005中的分页存储过程可以通过使用ROW_NUMBER()函数和OFFSET FETCH子句来实现，具体步骤包括：

1、使用ROW_NUMBER()函数为结果集中的每一行分配一个唯一的序号。

2、根据序号范围筛选出需要的页数据。

3、使用输出参数返回总记录数。

Q2: 如何在存储过程中设置和使用输出参数？

A2: 在存储过程中，可以使用DECLARE语句声明输出参数，并使用SET语句为其赋值，在调用存储过程时，需要使用sp_executesql系统存储过程来执行动态SQL，并通过输出参数获取结果。

 DECLARE @TotalRecord INT; EXEC sp_executesql @SqlString, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;

Q3: 如何优化分页查询的性能？

A3: 为了优化分页查询的性能，可以考虑以下几点：

1、使用索引：确保用于排序和过滤的列上有适当的索引。

2、减少返回的数据量：只选择必要的字段，避免使用SELECT。

3、使用缓存：对于不经常变动的数据，可以考虑使用缓存机制来减少数据库访问次数。

在 SQL Server 2005 中实现存储过程分页，通常会使用ROW_NUMBER() 函数和OFFSETFETCH 子句，以下是一个分页存储过程的示例代码：

 CREATE PROCEDURE GetPagedRecords     @PageSize INT,     @PageNumber INT AS BEGIN     计算跳过的记录数     DECLARE @Skip INT;     SET @Skip = (@PageNumber 1) * @PageSize;     假设我们要分页的表名为 'YourTable'，并且有一个可以排序的列名为 'SortColumn'     使用 ROW_NUMBER() 为每行分配一个序号，然后通过 OFFSET 和 FETCH 子句实现分页     SELECT *     FROM (         SELECT ROW_NUMBER() OVER (ORDER BY SortColumn) AS RowNum, *         FROM YourTable     ) AS NumberedRecords     WHERE RowNum > @Skip     AND RowNum <= @Skip + @PageSize; END GO

在这个存储过程中：

@PageSize 是每页显示的记录数。

@PageNumber 是请求的页码。

@Skip 计算出需要跳过的记录数。

ROW_NUMBER() 函数为YourTable 表中的每行生成一个唯一的序号，按照SortColumn 列进行排序。

WHERE 子句中的RowNum > @Skip AND RowNum <= @Skip + @PageSize 确保只选择当前页的记录。

要调用这个存储过程，你可以使用以下 SQL 语句：

 EXEC GetPagedRecords @PageSize = 10, @PageNumber = 1;

这会返回第一页的记录，每页有 10 条记录。

ORDER BY 子句是分页的关键，它决定了记录的排序方式，你应该根据实际需要排序的列替换SortColumn，如果你的表有多个排序列，可以使用ORDER BY 子句中的多个列来定义排序顺序。