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