Microsoft® SQL Server® 2012 Bible

411

Chapter 16: Programming with T-SQL

16

USE AdventureWorks2012;

GO

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET 2 ROWS

FETCH NEXT 5 ROWS ONLY;

Result:

DepartmentID Name

------------ ---------------------------

3 Sales

4 Marketing

5 Purchasing

6 Research and Development

7 Production

This is quite powerful and allows results paging to be handled server-side rather than cli-

ent-side as it has traditionally been done. If you need to return all rows but want to break

them into individual pages with a given number of rows per page, the following example

should do just that for you.

DECLARE @StartRow int = 1,

@RowsPerPage int = 4

WHILE (SELECT count(*) FROM HumanResources.Department) >= @StartRow

BEGIN;

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET @StartRow -1 ROWS

FETCH NEXT @RowsPerPage ROWS ONLY;

SET @StartRow = @StartRow + @RowsPerPage;

END;

Results (partial):

DepartmentID Name

------------ --------------------------

1 Engineering

2 Tool Design

3 Sales

4 Marketing

c16.indd 411c16.indd 411 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM

http://www.it-ebooks.info