sql存储过程分页

### SQL存储过程分页知识点详解 #### 一、SQL Server 2000 分页逻辑方法 在SQL Server 2000中实现分页功能通常较为复杂，因为该版本的数据库管理系统并未提供内置的分页支持。因此，开发者需要通过自定义存储过程来实现分页逻辑。 ##### 1. 存储过程设计思路 - **输入参数**：存储过程接受一系列输入参数，包括表名、主键或唯一键字段、当前页码、每页显示的记录数、需要显示的字段列表、排序字段列表、查询条件以及输出参数——总记录数。 - **输出参数**：存储过程返回分页后的数据和总记录数。 ##### 2. 实现步骤 - **输入参数验证**：首先对输入参数进行有效性检查，如表名是否存在、是否指定了主键等。 - **分页字段检查**：如果未指定主键，则提示错误信息并退出执行。 - **参数规范化**：确保参数格式正确，例如如果未指定显示字段，则默认选择所有字段。 - **总记录数计算**：如果`@RecordCount`参数为空，则先计算总的记录数。 - **分页查询**： - 第一页查询：直接使用`TOP`关键字获取前N条记录。 - 非第一页查询：采用子查询的方式，结合主键或唯一键来定位记录的位置。 ##### 3. 示例代码解析 ```sql ALTER PROC [dbo].[PROCE_PageView2000] -- 定义存储过程 ( @tbname nvarchar(100), -- 表名 @FieldKey nvarchar(1000), -- 主键或唯一键字段 @PageCurrent int = 1, -- 当前页码 @PageSize int = 10, -- 每页记录数 @FieldShow nvarchar(1000) = '', -- 显示字段 @FieldOrder nvarchar(1000) = '', -- 排序字段 @WhereString nvarchar(1000) = N'', -- 查询条件 @RecordCount int OUTPUT -- 总记录数 ) AS SET NOCOUNT ON -- 参数有效性检查 -- IF OBJECT_ID(@tbname) IS NULL BEGIN RAISERROR('对象"%s"不存在', 1, 16, @tbname) RETURN END -- IF OBJECTPROPERTY(OBJECT_ID(@tbname), 'IsTable') = 0 AND OBJECTPROPERTY(OBJECT_ID(@tbname), 'IsView') = 0 AND OBJECTPROPERTY(OBJECT_ID(@tbname), 'IsTableFunction') = 0 BEGIN RAISERROR('"%s"不是表、视图或者表值函数', 1, 16, @tbname) RETURN END -- 分页字段检查 IF ISNULL(@FieldKey, '') = '' BEGIN RAISERROR('分页处理需要主键（或者惟一键）', 1, 16) RETURN END -- 其他参数检查及规范 IF ISNULL(@PageCurrent, 0) < 1 SET @PageCurrent = 1 IF ISNULL(@PageSize, 0) < 1 SET @PageSize = 10 IF ISNULL(@FieldShow, '') = '' SET @FieldShow = '*' IF ISNULL(@FieldOrder, '') = '' SET @FieldOrder = '' ELSE SET @FieldOrder = 'ORDER BY' + LTRIM(@FieldOrder) IF ISNULL(@WhereString, '') = '' SET @WhereString = '' ELSE SET @WhereString = 'WHERE(' + @WhereString + ')' -- 如果@RecordCount为NULL值,则计算总页数 IF @RecordCount IS NULL BEGIN DECLARE @sql nvarchar(4000) SET @sql = 'SELECT @RecordCount = COUNT(*) FROM ' + @tbname + @WhereString EXEC sp_executesql @sql, N'@RecordCount int OUTPUT', @RecordCount OUTPUT END -- 计算分页显示的TOPN值 DECLARE @TopN varchar(20), @TopN1 varchar(20) SELECT @TopN = @PageSize, @TopN1 = (@PageCurrent - 1) * @PageSize -- 第一页直接显示 IF @PageCurrent = 1 EXEC (N'SELECT TOP ' + @TopN + N' ' + @FieldShow + N' FROM ' + @tbname + @WhereString + @FieldOrder) ELSE BEGIN -- 处理别名 IF @FieldShow = '*' SET @FieldShow = 'a.*' -- 生成主键(惟一键)处理条件 DECLARE @Where1 nvarchar(4000), @Where2 nvarchar(4000), @s nvarchar(1000), @Field sysname SELECT @Where1 = '', @Where2 = '', @s = @FieldKey WHILE CHARINDEX(N',', @s) > 0 SELECT @Field = LEFT(@s, CHARINDEX(N',', @s)) ``` #### 二、SQL Server 2005 分页函数方法 随着SQL Server 2005的发布，新增了ROW_NUMBER()窗口函数，这使得分页操作变得更加简单高效。 ##### 1. 使用ROW_NUMBER()函数 - **ROW_NUMBER() OVER()**：该函数会为每一行分配一个唯一的连续整数。 - **子查询**：通过子查询将ROW_NUMBER()的值与分页参数结合，从而筛选出特定页的数据。 ##### 2. 示例代码 ```sql WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY your_sort_column) AS RowNum FROM your_table_name WHERE your_where_condition ) SELECT * FROM CTE WHERE RowNum BETWEEN @PageNumber * @PageSize + 1 AND (@PageNumber + 1) * @PageSize ``` 在SQL Server 2000中实现分页功能较为复杂，需要自定义存储过程，并通过一系列复杂的逻辑判断和查询语句来完成。而在SQL Server 2005中，利用ROW_NUMBER()函数可以使分页操作更加简便高效。