T-SQL Dynamic Paged Query Techniques

Here we will see how to write dynamic paged query using Derived Table and CTE.

Simple paging query using Derived Table:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from

( select  row_number() over(order by EmployeeId) as Row,* from HrEmployee ) as e

where e.Row between 21 and 30 
END

Dynamic paging query using Derived Table:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

      @StartRowIndex        int,

      @RowPerPage       int,

      @WhereClause      nvarchar(4000),

      @SortColumn       nvarchar(128),

      @SortOrder        nvarchar(4)

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      SET @StartRowIndex = isnull(@StartRowIndex, -1)

      SET @RowPerPage = isnull(@RowPerPage, -1)

      SET @WhereClause = isnull(@WhereClause, '')

      SET @SortColumn = isnull(@SortColumn, '')

      SET @SortOrder = isnull(@SortOrder, '')

DECLARE @SQL nvarchar(4000)

      IF (@WhereClause != '')

      BEGIN

            SET @WhereClause = 'WHERE ' + char(13) + @WhereClause

      END

      IF (@SortColumn != '')

      BEGIN

            SET @SortColumn = 'ORDER BY ' + @SortColumn

            IF (@SortOrder != '')

                  BEGIN

                        SET @SortColumn = @SortColumn + ' ' + @SortOrder

                  END

      END

      SET @SQL = 'SELECT * FROM (SELECT *,

                        ROW_NUMBER() OVER ('+ @SortColumn +')AS Row

                        FROM  [HrEmployee]

                        '+ @WhereClause +'

                        ) as E

                         

                        WHERE E.Row between '+ CONVERT(nvarchar(10), @StartRowIndex) +' And ('+ CONVERT(nvarchar(10), @StartRowIndex+ @RowPerPage-1) +')'

--    print @SQL

EXEC sp_executesql @SQL

END

Simple paging query using CTE:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

WITH e AS (select  row_number() over(order by EmployeeId) as Row,* from HrEmployee )

select * from e where e.Row between 21 and 30
END

Dynamic paging query using CTE:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

      @StartRowIndex        int,

      @RowPerPage       int,

      @WhereClause      nvarchar(4000),

      @SortColumn       nvarchar(128),

      @SortOrder        nvarchar(4)

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      SET @StartRowIndex = isnull(@StartRowIndex, -1)

      SET @RowPerPage = isnull(@RowPerPage, -1)

      SET @WhereClause = isnull(@WhereClause, '')

      SET @SortColumn = isnull(@SortColumn, '')

      SET @SortOrder = isnull(@SortOrder, '')

DECLARE @SQL nvarchar(4000)

      IF (@WhereClause != '')

      BEGIN

            SET @WhereClause = 'WHERE ' + char(13) + @WhereClause

      END

      IF (@SortColumn != '')

      BEGIN

            SET @SortColumn = 'ORDER BY ' + @SortColumn

            IF (@SortOrder != '')

                  BEGIN

                        SET @SortColumn = @SortColumn + ' ' + @SortOrder

                  END

      END

      SET @SQL = 'WITH E AS (

                        SELECT ROW_NUMBER() OVER ('+ @SortColumn +')AS Row,  *

                        FROM  [HrEmployee]

                        '+ @WhereClause +'

                        )

                        SELECT *

                        FROM E

                        WHERE E.Row between '+ CONVERT(nvarchar(10), @StartRowIndex) +' And ('+ CONVERT(nvarchar(10), @StartRowIndex+ @RowPerPage-1) +')'

--    print @SQL

EXEC sp_executesql @SQL

END