Pagination : CTE. Row_Number() - Tech Head Brothers

Pagination : CTE. Row_Number()

Paginer un jeu d'enregistrement avec SQL Server 2005

Par Sébastien Pertus publié le 25/10/2007 à 18:14, lu 6979 fois,

0 commentaire(s) tag(s)

Tags: SQL Server 2005

Introduction

Aujourd'hui petite astuce pour faire de la pagination de données avec SQL Server 2005 :

Vous avez suremment déjà "exploré" les solutions mises en oeuvres par les scripts SQL des MemberShip Providers pour faire de la pagination de données.
Et comme moi, peut être, vous avez trouvé ça quelque peu ... lourd.

Bon, en même temps, ces scripts se veulent compatibles avec SQL Server 2000 par exemple. De ce fait, les cht'i gars de chez MS n'avaient guère le choix ...

Mais nous, si !
Alors comment faire pour récupérer un jeu de données paginé ?
Tout d'abord, nous allons utiliser Row_Number(), qui a la bonne idée de retourner un numéro de ligne
Par exemple :

Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID, E.ManagerID,

E.BirthDate From HumanResources.Employee E

retourne :

1 268 273 1941-11-17
2 246 51 1942-04-03
3 238 274 1942-06-13
4 9 3 1942-10-29
5 237 108 1943-06-01
6 260 49 1944-05-25
7 243 18 1945-03-02
8 7 21 1946-02-16
9 10 185 1946-04-27
10 19 185 1946-04-30
11 14 21 1946-05-03
12 17 185 1946-05-06
Etc ...

Malheuresement, Row_Number() est analysé par le moteur APRES la clause WHERE.Du coup interdiction de faire quelque chose du genre :

where Row_Number() over (order by BirthDate asc) > 100

and Row_Number() over (order by BirthDate asc) < 120

D'où l'idée d'utiliser une Common Type Expression, qui aura la bonne idée de nous fournir notre précieuse information !
Ce qui donne dans notre exemple :

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)

(

Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID,

E.ManagerID, E.BirthDate

From HumanResources.Employee E

)

Select *

From mCte C

Where RowNumber Between 100 and 120

Avec ASP.Net et une data source, il est de bon ton de donner un numéro de page et un nombre d'enregistrements à récupérer.

Si on arrange tout ça pour s'adapter à ce contexte, on obtient une procédure stockée du style :

CREATE procedure [GetEmployeesPaged]

(

@nStartRowNum int = 100,

@nStartRowCount int = 10

)

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)

(

Select Row_Number() over (order by BirthDate asc) as RowNumber,

E.EmployeeID, E.ManagerID, E.BirthDate

From HumanResources.Employee E

)

Select *

From mCte C

Where RowNumber Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

Pour aller plus loin :

Si on pousse un peu, je vous propose une Double CTE pour récupérer non seulement les employés mais aussi les managers de ceux ci, ce qui va nous obliger à faire une boucle récursive, ce qui est naturel avec une CTE.
bref, plus que des mots ... du SQL !

With

EmpCTE (EmpID, MgrID, Level)

As (

Select E.EmployeeID, E.ManagerID, 1

From HumanResources.Employee E

Where ManagerID is null

UNION ALL

Select E.EmployeeID, E.ManagerID, Level + 1

From HumanResources.Employee E

Inner Join EmpCTE on EmpCTE.EmpID = E.ManagerID

myCTE

As (

Select Row_Number() over (order by Level asc) as RowNum, *

From EmpCTE

)

Select C.RowNum, C.EmpID, CE.FirstName+ ' ' + CE.LastName as EmpName,

C.MgrID, ME.FirstName+ ' ' + ME.LastName as MgrName, C.Level

From myCTE C

Inner Join Person.Contact CE on C.EmpID = CE.ContactID

Left Outer Join Person.Contact ME on C.MgrID = ME.ContactID

Where RowNum Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

Un peu de SQL bien pensé et nous voilà paré !

» Démarrer une discussion