How to add pagination to this dynamic sql

I have the following table with its respective data

CREATE TABLE [dbo].[Columns]
(
    [ColumnId] INT,
    [TableId] INT NOT NULL,
    [ColumnName] NVARCHAR(150) NOT NULL,
    [Order] INT,
    [Key] BIT
)

CREATE TABLE [dbo].[Tables]
(
    [TableId] INT,
    [TableName] NVARCHAR(200),
    [DistrictId] INT
)

CREATE TABLE [dbo].[RowValues]
(
    [ColumnId] INT NOT NULL,
    [RowNumber] INT NOT NULL,
    [Value] NVARCHAR(200) NOT NULL
)


insert into [Columns] values (1, 1, 'StudentName', 1, 1)
insert into [Columns] values (2, 1, 'Grade', 1, 0)
insert into [Columns] values (3, 1, 'Year', 1, 0)
insert into [Columns] values (4, 1, 'Section', 1, 0)

insert into [Columns] values (5, 2, 'TeacherName', 1, 1)
insert into [Columns] values (6, 2, 'Department', 1, 0)

insert into [Tables] values (1, 'Student', 1)
insert into [Tables] values (2, 'Teacher', 1)

insert into [RowValues] values (1, 1, 'Student Alan')
insert into [RowValues] values (2, 1, '99')
insert into [RowValues] values (3, 1, '1st')
insert into [RowValues] values (4, 1, 'Section 1')

insert into [RowValues] values (1, 2, 'Student Alex')
insert into [RowValues] values (2, 2, '98')
insert into [RowValues] values (3, 2, '1st')
insert into [RowValues] values (4, 2, 'Section 1')

insert into [RowValues] values (1, 3, 'Student Alfonso')
insert into [RowValues] values (2, 3, '97')
insert into [RowValues] values (3, 3, '1st')
insert into [RowValues] values (4, 3, 'Section 1')

insert into [RowValues] values (1, 4, 'Student Ben')
insert into [RowValues] values (2, 4, '96')
insert into [RowValues] values (3, 4, '1st')
insert into [RowValues] values (4, 4, 'Section 1')

insert into [RowValues] values (1, 5, 'Student Cathy')
insert into [RowValues] values (2, 5, '95')
insert into [RowValues] values (3, 5, '1st')
insert into [RowValues] values (4, 5, 'Section 1')

insert into [RowValues] values (5, 1, 'Teacher Tesso')
insert into [RowValues] values (6, 1, 'Biology Dept')

insert into [RowValues] values (5, 2, 'Teacher Marvin')
insert into [RowValues] values (6, 2, 'Math Dept')

I wanted to be able to sort the dynamic column example sort by ‘StudentName’ ASC it would sort data using column returning

When sorting by StudentName:

StudentName         Grade       Year        Section
'Student Alan'      99          '1st'       'Section 1'
'Student Alex'      98          '1st'       'Section 1'
'Student Alfonso'   97          '1st'       'Section 1'
.
.
.

When sorting by Grade ASC:

StudentName         Grade       Year        Section
'Student Cathy'     95          '1st'       'Section 1'
'Student Ben'       96          '1st'       'Section 1'
'Student Alfonso'   97          '1st'       'Section 1'
.
.
.

When sorting by TeacherName ASC (different table)

TeacherName         Department
'Teacher Marvin'    'Math Dept'
'Teacher Tesso'     'Biology Dept'
.
.
.

I have manage to accomplish this.

The problem is adding Pagination to my dynamic SQL string.

This is my stored procedure

CREATE PROCEDURE [dbo].[usp_DynamicSearch_Paged]
    (@districtId INT,
     @searchTerm NVARCHAR(max),
     @pageNumber INT = 1,
     @pageSize INT = 10,
     @sortColumn NVARCHAR(20),
     @sortDirection INT)
AS
BEGIN
    DECLARE @columns nvarchar(max),
            @sql nvarchar(max),
            @rows int

    SET @rows = (@pageNumber - 1) * @pageSize;

    SELECT 
        @columns = (SELECT ',' + QUOTENAME(ColumnName)
                    FROM Tables AS T
                    INNER JOIN Columns AS C ON T.TableId = C.TableId
                    WHERE T.districtId = @districtId
                    ORDER BY C.[Order]
                    FOR XML PATH(''))

    SELECT 
        @columns = (SELECT ',' + QUOTENAME(ColumnName)
                    FROM Tables AS T
                    INNER JOIN Columns AS C ON T.TableId = C.TableId
                    WHERE T.districtId = @districtId
                    ORDER BY C.[Order]
                    FOR XML PATH(''))

    SELECT [Columns] = REPLACE(REPLACE(value,'[',''),']','') 
    FROM STRING_SPLIT(STUFF(@Columns, 1, 1, ''),',')

    SET @sql = N'
    SELECT * INTO #Fields FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY ColumnName ORDER BY (SELECT NULL)) RN,
        districtId,
        ColumnName
        , V.[Value]
    FROM Tables AS T
    INNER JOIN Columns AS C
        ON T.TableId = C.TableId
    LEFT OUTER JOIN RowValues AS V
        ON C.ColumnId = V.ColumnId
    ) t
    PIVOT (
        MIN([Value])
        FOR [ColumnName]
        IN ('
            + STUFF(@Columns, 1, 1, '') +
        ')
    ) AS PivotTable
    
    DECLARE @KeyColumnName NVARCHAR(100)
    SELECT @KeyColumnName = ColumnName
    FROM Tables AS T
    INNER JOIN Columns AS C
        ON T.TableId = C.TableId
    WHERE T.districtId = ' + CAST(@districtId AS VARCHAR) + '
        AND C.IsKey = 1

    DECLARE @sql NVARCHAR(MAX)
    SET @SQL = ''
    SELECT *
    FROM #Fields
    WHERE '' + @KeyColumnName + '' LIKE '''''+ @searchTerm +'%''''
    ''
    OFFSET ('+CAST(@rows AS VARCHAR)+') ROWS
    FETCH NEXT ' +CAST(@pageSize AS VARCHAR) +' ROWS ONLY;
    EXEC sp_EXECUTESQL @sql
    '

EXEC sp_executesql @sql

END

I just added the

    OFFSET ('+CAST(@rows AS VARCHAR)+') ROWS
    FETCH NEXT ' +CAST(@pageSize AS VARCHAR) +' ROWS ONLY;
    EXEC sp_EXECUTESQL @sql

and now it doesn’t work

Answer

The OFFSET and FETCH clauses are the options of the ORDER BY clause. They allow you to limit the number of rows to be returned by a query. https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ I think you need to declare ORDER BY