Face à  un nombre de niveaux arbitraire

présents dans la
hiérarchie ; donc, le nombre de colonnes
varie en fonction des données.
Cet état de fait pose un problème
parce qu’il n’est pas possible de changer
le nombre de colonnes d’une instruction
SQL à  la volée. Les noms de
colonnes sont destinés à  être des labels
fixes pour définir le contenu des données
dans les lignes. Mais pour la table
universelle, Microsoft a décidé d’utiliser
des noms de colonnes pour stocker
des métadonnées.
Heureusement il y a de l’aide. On
peut utiliser SQL dynamique, qui permet
de construire toute instruction
SQL dans une procédure stockée puis
de l’exécuter en utilisant la commande
EXEC. Comme exemple simple d’utilisation
de SQL Server dynamique, appliquons
une requête simple à  la base
de données Pubs :

SELECT title FROM titles

On peut exécuter la même requête
en utilisant SQL dynamique de la façon
suivante :

DECLARE @SQL varchar(100)
SELECT @SQL = ‘SELECT title FROM
titles’
EXEC (@SQL)

Bien que les deux exemples de
code produisent le même jeu de résultats,
l’utilisation de SQL dynamique
dans une procédure stockée a son prix.
La première fois qu’on exécute une
procédure stockée, SQL Server procède
à  plusieurs vérifications avant de
la compiler et de l’exécuter. Entre
autres choses, il vérifie si la syntaxe est
correcte et si les références de colonnes
et de tables sont valides.
L’optimisateur de requête produit ensuite
un plan de requête qui détermine
quels index utiliser. SQL Server tient ce
plan de requête à  disposition chaque
fois que la procédure stockée s’exécute.
Rien de cela ne se produit avec SQL dynamique parce que le moteur
SQL Server ne sait pas quel SQL il recevra
tant que vous n’exécutez pas la requête.
Il s’en suit qu’avec SQL dynamique,
le risque d’erreur runtime est
plus grand et la performance moindre.
Heureusement, pour ce type de problème
de gestion (par exemple, une
hiérarchie de direction), les volumes
de données seront probablement modestes
et il devrait être assez facile
d’éviter les erreurs de syntaxe.
Le listing 5 montre toute la procédure
stockée qui vous permet d’utiliser
SQL dynamique pour traiter des niveaux
multiples, en la divisant en
tranches pour expliquer son fonctionnement.
Si vous chargez cette procédure
stockée dans SQL Server et l’exécutez
dans Query Analyzer, elle renvoie
le XML familier de la figure 2.
Au renvoi A du listing 5, la procédure
stockée construit une table temporaire
; le code du renvoi B peuple la
table avec une ligne pour chaque employé.
La table a une colonne supplémentaire
pour stocker la profondeur
de chaque noeud. En C, la procédure
ajoute la profondeur d’un noeud particulier
à  chaque enregistrement Employee
en passant par une boucle qui met
à  jour les enregistrements des employés
appropriés pour chaque niveau.
Au début, cela peut sembler inefficace
mais le code traverse la boucle une fois
pour chaque niveau, pas une fois pour
chaque employé. En pratique, la plupart
des arbres ont beaucoup plus de
noeuds que de niveaux ; par exemple,
une structure de direction pour une
société de 100 employés n’a probablement
que 5 niveaux et donc la boucle
sera traversée 5 fois au lieu de 100.
Notons que le code n’a pas encore
eu recours à  SQL dynamique. Il vaut
mieux en faire le plus possible avant
que cela ne devienne nécessaire. Dès
lors que vous commencez à  utiliser
SQL dynamique pour créer des tables,
vous ne pouvez pas revenir à  SQL ordinaire
parce que le compilateur ne
saura rien des nouvelles tables et produira
des messages d’erreur au motif de références invalides. Par conséquent,
pour rendre les choses plus faciles
ultérieurement, il vaut mieux
mettre autant de données que possible
dans la table temporaire #Level. Après
avoir peuplé la table, le code en D utilise
simplement une instruction SELECT
max() pour calculer la plus
grande profondeur, laquelle détermine
à  son tour le nombre de colonnes requises
pour que la table temporaire
renvoie le XML.
Le code en E commence à  construire
la chaîne qui créera la table temporaire
que j’ai décrite dans la section
Structure universelle. On a atteint le
point de non-retour : désormais, on
construit des chaînes SQL dynamique
et on les exécute avec EXEC(). Le code
en F ajoute les noeuds de niveau supérieur
(lequel, dans ce cas, est simplement
Fred). Après cela, le code en G
fait une boucle autour de chaque niveau
et ajoute les noeuds provenant de
ce niveau jusqu’à  ce qu’il arrive au plus
profond (le code en C a déjà  calculé la
valeur de ce niveau).

A ce stade, les choses sont un peu
plus difficiles à  comprendre. Malheureusement,
Query Analyzer n’aide pas
beaucoup parce qu’il ne met pas en
évidence par des couleurs les motsclés
pour des procédures stockées,
comme il le fait pour T-SQL normal. Un
moyen de contournement et une technique
utile pour le débogage consiste à 
utiliser une instruction PRINT @SQL à 
la place de l’instruction EXEC (@SQL).
Vous pouvez coller le code SQL dans
une autre fenêtre Query Analyzer et
l’exécuter pour voir la mise en évidence
de la syntaxe. Ce code a moins
de jointures parce que vous avez
chargé une grande partie des données
dans la table #Level. La dernière étape,
qu’illustre le renvoi H, consiste à  renvoyer
les données XML.