/****** Object:  StoredProcedure [dbo].[GetPagedData]    Script Date: 17.04.2024 21:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPagedData]
    @SqlQuery NVARCHAR(MAX),
    @PageSize INT,
    @PageNumber INT
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    DECLARE @DynamicSql NVARCHAR(MAX);


    SET @DynamicSql = '
        DECLARE @StartIndex INT = ' + CAST(@Offset + 1 AS NVARCHAR) + ';
        DECLARE @EndIndex INT = ' + CAST(@Offset + @PageSize AS NVARCHAR) + ';


        WITH OrderedData AS (
            ' + @SqlQuery + '
        )
        SELECT *
        FROM OrderedData
        WHERE RowNumber BETWEEN @StartIndex AND @EndIndex;
    ';


    EXEC sp_executesql @DynamicSql;
END;


/*
for using example:

DECLARE @Query NVARCHAR(MAX);
SET @Query = '
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM SampleTable
    ';


EXEC GetPagedData @SqlQuery = @Query, @PageSize = 10000, @PageNumber = 2;
*/