CTE Data Paging in a Procedure

Download PDF

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.
DataPagingSproc1

CTE Related Posts:

 

Tagged with: , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.