分页系列之一：SQL Server 分页存储过程

时间:2021-04-26 羊茂林人气:0

以下为最基本的代码结构，SQL Server 2012 开始支持

CREATE PROCEDURE procXXX
    @Page int,    --当前页码，从1开始 
    @PageSize int    --每页记录数
AS
BEGIN
    SET NOCOUNT ON
    SELECT * 
    FROM tbTest 
    ORDER BY ID
    OFFSET @PageSize*(@Page-1) ROW FETCH NEXT @PageSize ROWS ONLY
    --查询总记录数，前台分页需要
    SELECT COUNT(*) FROM tbTest 
END

实际使用时，可以采用拼接SQL的方式，因为两个查询语句的查询条件相同，这样只需要拼接一次查询条件，另外，如果需要动态排序，也需要拼接SQL，因为排序条件不接受SQL参数

CREATE PROCEDURE procXXX
    @Page int,        --页索引
    @PageSize int,    --每页记录数
    @Number nvarchar(50) = '',    --产品编号
    @Status smallint = -1    --状态
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sql nvarchar(MAX), @condition nvarchar(MAX)
    SET @condition = '1=1'
    IF @Number <> ''
    BEGIN
        SET @condition += ' AND Number LIKE ''%' + @Number + '%'''
    END
    IF @Status <> -1
    BEGIN
        SET @condition += ' AND Status = ' + STR(@Status)
    END
    SET @sql='SELECT ID,Number
        FROM tbProduct
        WHERE '+@condition+' 
        ORDER BY ID DESC
        OFFSET ' + STR(@PageSize*(@Page-1)) + ' ROW FETCH NEXT ' + STR(@PageSize) + ' ROWS ONLY
        SELECT COUNT(*)
        FROM tbProduct
        WHERE '+@condition
    EXECUTE(@sql)
END

以下为旧版的分页存储过程，支持早期SQL Server版本，必须使用拼接SQL的方式，因为TOP后面不接受参数。

create procedure procXXX
    @Page int,    --页索引 
    @PageSize int    --每页记录数 
as
begin
    set nocount on;
    declare @sql nvarchar(1000)
    declare @condition nvarchar(500)
    set @condition = '1=1'
    set @sql='SELECT TOP '+str(@PageSize)+' * 
        FROM tbTest 
        WHERE '+@condition+' 
        AND (ID NOT IN(SELECT TOP '+str(@PageSize*(@Page-1))+' ID 
            FROM tbTest WHERE ID > 0 '+@condition+' 
            ORDER BY ID)) 
        ORDER BY ID
        SELECT COUNT(*) FROM tbTest WHERE '+@condition
    execute(@sql)
end

还有其他分页方式，比如从 SQL Server 2005 开始，可以使用 ROW_NUMBER，这里就不列举了。

加载全部内容