Conclusions

à 100. Vous pouvez outrepasser cette limite en fixant vous-même la valeur à l’aide de la clause OPTION (MAXRECURSION n), qui doit figurer en dernier dans la requête.

Enfin, sachez que la norme SQL:1999 propose des compléments syntaxiques pour piloter votre SQL récursif. Par exemple vous pouvez naviguer dans les données DEPTH FIRST ou BREADTH FIRST (en profondeur ou en largeur en premier lieu) et aussi constituer une colonne contenant toutes les données des étapes intermédiaires dans un tableau de ligne (ARRAY of ROW) dont la taille doit être "suffisante" pour couvrir tous les cas de figure. Voici la syntaxe complète de la CTE avec récursivité :

WITH [ RECURSIVE ] [ ( <liste_colonne> ) ] AS ( <requete_select> ) [ <clause_cycle_recherche> ] with : <clause_cycle_recherche> ::= <clause_recherche> | <clause_cycle> | <clause_recherche> <clause_cycle> and : <clause_recherche> ::= SEARCH { DEPTH FIRTS BY | BREADTH FIRST BY } <liste_specification_ordre> SET <colonne_sequence> <clause_cycle> ::= CYCLE <colonne_cycle1> [ { , <colonne_cycle2> } … ] SET <colonne_marquage_cycle> TO <valeur_marque_cycle> DEFAULT <valeur_marque_non_cycle> USING <colonne_chemin>

En bonus (CTE, requête récursive appliquée)

Voici une procédure stockée qui donne l’ordre exact des DELETE à effectuer dans des tables afin de vider une table de ses données, lorsque cette dernière est liée par l’intégrité référentielle :

CREATE PROCEDURE P_WHAT_TO_DELETE_BEFORE
@TABLE_TO_DELETE VARCHAR(128), — targettable to delete
@DB VARCHAR(128), — target database
@USR VARCHAR(128) — target schema (dbo in most cases)
AS

WITH

T_CONTRAINTES (table_name, father_table_name)
AS (SELECT DISTINCT CTU.TABLE_NAME, TCT.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RFC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU
ON RFC.CONSTRAINT_CATALOG = CTU.CONSTRAINT_CATALOG
AND RFC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA
AND RFC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON RFC.UNIQUE_CONSTRAINT_CATALOG = TCT.CONSTRAINT_CATALOG
AND RFC.UNIQUE_CONSTRAINT_SCHEMA = TCT.CONSTRAINT_SCHEMA
AND RFC.UNIQUE_CONSTRAINT_NAME = TCT.CONSTRAINT_NAME
WHERE CTU.TABLE_CATALOG = @DB AND CTU.TABLE_SCHEMA = @USR),
T_TREE_CONTRAINTES (table_to_delete, level)
AS (SELECT DISTINCT table_name, 0
FROM T_CONTRAINTES WHERE father_table_name = @TABLE_TO_DELETE
UNION ALL SELECT priorT.table_name, level – 1
FROM T_CONTRAINTES priorT
INNER JOIN T_TREE_CONTRAINTES
beginT ON beginT.table_to_delete = priorT.father_table_name
WHERE priorT.father_table_name <> priorT.table_name)
SELECT DISTINCT * FROM T_TREE_CONTRAINTES
ORDER BY level

GO

Le cas d’auto référence est en principe intégré. Les paramètres sont :
@DB : nom de la base,
@USR : nom du schema, par défaut dbo,
@TABLE_TO_DELETE : nom de la table que vous voulez vider