CTE Data Paging in a Procedure

Day 15 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at data paging with CTE’s in a function. Yesterday we took a look at CTEs for Data Paging, today we will build on what we used yesterday.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The concept of data paging is taking a large set of results and breaking it up into smaller pages of result sets. For instance if you search your favorite internet search engine for a term, you are usually shown results 1 to 20 of X where X is a very large number.  For instance page 1 is results 1 to 20 of 652,443 results.

CTE Data Paging

declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
)
SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;

Now lets wrap it in a procedure to simplify things.

CREATE PROCEDURE TablesAndColumnsPager @pageNum int, @pageSize int
AS
BEGIN
SET NOCOUNT ON;

;WITH TablesAndColumns AS
  (
SELECT OBJECT_NAME(sc.object_id) AS TableName,
  name AS ColumnName,
  ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
  FROM sys.columns sc
  )
  SELECT *
  FROM TablesAndColumns
  WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
  AND @pageNum * @pageSize ;
END

Then to call the paging function it is a bit cleaner.

exec TablesAndColumnsPager 1, 10;
exec TablesAndColumnsPager 2, 10;
exec TablesAndColumnsPager 3, 10;

Which produces the following output.

CTE Related Posts:

• Writing Your First Common Table Expression with SQL Server
• Video: Writing Your First CTE with SQL Server
• Common Table Expressions – Terminating the Previous Statement
• CTE Scope
• Introduction to Recursive CTEs
• Recursive CTE for Dates In A Year
• Expanding on Recursive Dates CTE
• Multiple CTEs in a Query
• Nested CTEs
• Nested CTE’s Gone Wild – The Video
• CTE Data Paging
• CTE Data Paging in a Procedure
• Using a CTE in a Function to Split Up a Query String

• Using a CTE to Split a String Into Rows
• Can we use CTEs use in SSRS?
• CTE Hierarchy compared to the alternative
• Calculating Factorials with a Recursive CTE
• CTE – With An Insert Statement
• Fibonacci Sequence
• Generating a Tree Path with a CTE
• CTE Query Performance
• Multiple CTE’s in a single Query
• Recursive CTE’s

• TSQL 2012 – OFFSET and FETCH

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!