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;
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