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

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

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