Examen du code

est téléchargeable à l’adresse http://www.itpro.fr Club Abonnés.

Le code T-SQL du bloc A du listing 1 commence par déterminer si la procédure stockée à créer existe déjà. Si c’est le cas, le code la supprime. Il utilise la commande CREATE COMMAND dans le bloc B pour générer la procédure stockée sp_CreateUpdateProc, laquelle contient deux paramètres : le nom et le propriétaire de la table. Le nom de la procédure stockée et les paramètres qu’elle passe sont placés en commentaire, de sorte que vous pouvez sélectionner ce texte en commentaire avec la souris au lieu de le retaper, comme nous le verrons plus tard. Le bloc de code C déclare toutes les variables requises et termine en définissant DBO en tant que propriétaire de la table, si ce dernier a la valeur null.

Ensuite, le code doit déterminer si l’objet avec le nom demandé existe et s’assurer que cet objet est une table. La requête du bloc D accomplit cette tâche en récupérant le type d’objet de la vue INFORMATION_SCHEMA.TABLES. Le code fait appel à la fonction système Object_ID avec @objectname en tant que paramètre, afin de déterminer si l’ID d’objet existe (à savoir, n’est pas null). Si l’ID d’objet est inexistant, le code renvoie le message d’erreur « OBJECT @objectname DOES NOT EXIST ». De même, le code du bloc E référence la fonction système Object_ID afin de déterminer si @objectname est une table. Si ce n’est pas le cas, le code génère le message d’erreur « OBJECT @objectname IS NOT A TABLE ».

Au cours des étapes suivantes, le code extrait les informations de colonne de la vue INFORMATION_SCHEMA.COLUMNS. Pour démarrer ce processus, le code du bloc F crée une table temporaire #TB_SCHEMA, afin de conserver ces informations de colonne. Le code analyse cette table trois fois afin de créer une liste de paramètres, une instruction SET et des arguments de recherche. Le tableau 1 illustre la structure de cette table temporaire. Maintenant, le code du bloc G remplit la table #TB_ SCHEMA au moyen d’une instruction SELECT. Notez que la colonne PK_Flag est initialisée à 0. L’instruction UPDATE du bloc H définit PK_Flag à 1 pour toutes les colonnes du type clé primaire dans la table #TB_SCHEMA. Le code identifie les colonnes en question en interrogeant deux vues : INFORMATION_ SCHEMA.CONSTRAINT_COLUMN_USAGE et INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

Examinons ces deux vues: INFORMATION_SCHEMA.CONSTRAINT_ COLUMN_USAGE contient les noms de contraintes et les noms de colonnes en référence croisée. INFORMATION_ SCHEMA.TABLE_CONSTRAINTS contient des informations sur les propriétés de contrainte, y compris leur type. En associant ces deux vues à la table #TB_SCHEMA, vous pouvez identifier les colonnes de cette table qui font partie de la contrainte de clé primaire.

Le code du bloc I détermine si la contrainte de clé primaire existe. Si ce n’est pas le cas, le code génère le message d’erreur : « Object "nom_table" DOES NOT HAVE A PRIMARY KEY ». L’étape suivante consiste à supprimer les colonnes que le code ne peut pas référencer dans l’instruction UPDATE, comme l’illustre le code du bloc J. Ces types de données de colonne sont text, ntext, image, timestamp, rowversion, ainsi que la colonne identity, qui n’est pas une contrainte de clé primaire. Notez que le code peut référencer une colonne identity dans une instruction UPDATE en tant qu’argument de recherche, mais pas dans une clause SET. A ce stade, la table #TB_SCHEMA contient toutes les informations requises pour le processus de génération de code.

Dans l’instruction T-SQL CREATE PROCEDURE du bloc K, le nom de la procédure stockée générée est basé sur le nom de la table. Dans un premier temps, le code supprime la procédure stockée si elle existe déjà et, si le nom de table contient des espaces, le code les remplace par un caractère de soulignement.

Ensuite, le code du bloc L génère la liste de paramètres. En vue d’obtenir des données pour cette liste, le code extrait les données de la table temporaire #TB_SCHEMA qu’il a créée précédemment. Chaque ligne dans la table correspond à un paramètre distinct. Deux méthodes sont disponibles pour effectuer le traitement ligne par ligne. Une approche consiste à déclarer un curseur. La deuxième solution, que nous croyons plus élégante et plus efficace, consiste à générer des colonnes contenant des valeurs uniques.

Comme vous l’avez probablement remarqué, la table temporaire a une colonne identity intitulée RowNo. Pour générer ces colonnes à valeurs uniques, le code du bloc L utilise d’abord les fonctions MAX() et MIN() pour récupérer les valeurs maximum et minimum de la colonne RowNo. Ces valeurs vont devenir la première et la dernière ligne de la table temporaire. Ensuite, le code extrait les données de ligne au moyen de la valeur @RowNo qu’il vient d’obtenir pour cette ligne sous forme de clé. Le code commence à créer la liste de paramètres à partir du type de données de chaque colonne concernée. Le code utilise la variable @pRowNo pour stocker la ligne en cours de traitement.

L’instruction SELECT finale du bloc L récupère @RowNo en utilisant de nouveau la fonction MIN() et la valeur @RowNo courante en tant qu’argument de recherche si le numéro de la ligne suivante est supérieur à celui de la ligne courante. Cette section de code vérifie également qu’elle ne traite pas la dernière ligne et, tant que cette condition est vérifiée, le code ajoute une virgule à chaque ligne afin de séparer les éléments dans la liste. Le code continue de traiter le bloc BEGIN… END jusqu’à ce qu’il n’y ait plus deux lignes successives à récupérer. Les étapes finales du bloc L consistent à générer la définition pour @ERRORCODE et à générer une ligne de commentaire indiquant à quel endroit placer toute logique de validation supplémentaire nécessaire.

Le bloc de code M crée l’instruction UPDATE. Cette opération requiert de parcourir une fois de plus la table #TB_SCHEMA afin de créer la clause WHERE et la liste SET. Pour créer la clause WHERE, le code se sert de la contrainte de clé primaire en tant qu’argument de recherche. Si la colonne à mettre à jour appartient à la contrainte de clé primaire, le code ajoute à la clause WHERE une chaîne ayant le format = @ ; si la colonne ne fait pas partie de la contrainte de clé primaire, le code imprime la chaîne en tant que partie de la clause SET. La dernière section du listing 1, illustrée par le bloc N, sert à la gestion des erreurs. Ce code de base contient un élément intéressant : l’utilisation de la fonction système OBJECT_NAME(). Le fait de passer @@PROCID en tant que paramètre à cette fonction retournera le nom de la procédure stockée créée par le listing 1. Pour fournir un message d’erreur suffisamment descriptif tout en conservant le caractère générique du code, vous devez afficher le nom de la procédure stockée dans le message d’erreur. Notez que vous pouvez modifier facilement le code de gestion des erreurs et la clause WHERE afin de les adapter aux exigences de votre application.