亲宝软件园·资讯

展开

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

羊茂林 人气: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,这里就不列举了。

加载全部内容

相关教程
猜你喜欢
用户评论